Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I have some code in which I am placing a formula in certain cells, but if the sheet name is "Corporate Departments", I want to to replace the original formula with something else. I have been trying to write a replacement statement but it is not working properly. This is the code I am writing:

    Selection.Replace What:="-F" & ACPos & ", Replacement:="-(F" & ACPos & "-L" & ACPos & ")" , LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    I keep getting an error and I can't wrap my mind around what is incorrect.Could someone assist? Below is the (1) original formula and the the (2) replacement formula.

    (1) Original Formula
    Range("$I$" & ACPos).Formula = "=IF(OR(C" & ACPos & "-F" & ACPos & "<-1,C" & ACPos & "-F" & ACPos & ">1)=TRUE,TRUNC(C" & ACPos & "-F" & ACPos & "),0)"

    (2) Replacement Formula

    Range("$I$" & ACPos).Formula = "=IF(OR(C" & ACPos & "-(F" & ACPos & "-L" & ACPos & ")" & "<-1,C" & ACPos & "-(F" & ACPos & "-L" & ACPos & ") & >1)=TRUE,TRUNC(C" & ACPos & "-(F" & ACPos & "-L" & ACPos & "),0)"

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    Selection.Replace What:="-F" & ACPos, Replacement:="-(F" & ACPos & "-L" & ACPos & ")" , LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    I think.


    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    You could save the formulae as constants and use the appropriate one. Nice and easy to change the formula if required.

    cheers, Paul

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Flint, Michigan
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your statement has a quotation mark before the comma and word Replacement. Change this:

    Selection.Replace What:="-F" & ACPos & ", Replacement:="-(F" & ACPos & "-L" & ACPos & ")" , LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    to this:

    Selection.Replace What:="-F" & ACPos & , Replacement:="-(F" & ACPos & "-L" & ACPos & ")" , LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    --Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •