Replacing formula values

Topic

New Reply

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)=TRUE,TRUNC(C” & ACPos & “-F” & ACPos & “),0)”

(2) Replacement Formula

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

Reply | Quote

Replies

Code:

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

I think.

Reply | Quote

You could save the formulae as constants and use the appropriate one. Nice and easy to change the formula if required.

cheers, Paul

Reply | Quote

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

Reply | Quote