Replace Text in Formula VBA

[campbesj7]

Hi there,

I am trying to update my formulas in VBA to reflect the current month, by overwriting the previous the now show the current.
Dim FindText as String
Dim ReplaceText as String

FindText = OffsetMonth 'Previous month
ReplaceText = SheetName 'Current month
'Replace current month
Sheets("BY TECH").Range(A1, AT102).Select
Replace What:=FindText, Replacement:=ReplaceText, LookAt:=xlPart, MatchCase:=False

The formulas in the cell look like: ='JAN START'!B4
I am trying to replace everywhere it says JAN with FEB and so on etc.

I keep getting a Named argument not found at the What:= location

Any help would be greatly appreciated!
Thanks

 

[JoeMo]

Range.Replace is looking for values not formulas. Try this:

Sub TEST()
Dim c As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each c In Sheets("BY TECH").Range(A1, AT102).SpecialCells(xlFormulas)
    c.Formula = Replace(c.Formula, "JAN", "FEB")
Next c
Application.ScreenUpdating = True
End Sub

 

[campbesj7]

Thank you for your help Joe. Unfortunately, using that code, nothing changes within that sheet. I tried recording the macro, and got the following code:

Range("A1:AT100").Select
Selection.Replace What:="JAN", Replacement:="FEB", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I now get an "Select Method of Range Class Failed" error on the first line,

 

[campbesj7]

Great news! I was able to get the above code to work. I had to modify it to be the following:

Sheets("BY TECH").Range("A1:AT102").Select
Selection.Replace What:="FEB", Replacement:="MAR", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Thanks for your help!

 

[Joe4]

Great news! I was able to get the above code to work. I had to modify it to be the following:

Sheets("BY TECH").Range("A1:AT102").Select

Sounds like you weren't on the "BY TECH" sheet when you call the code to run (if you leave off the Sheet reference, it usually defaults to try to run on the active sheet, whatever it is at the time the code is called).

 

[campbesj7]

You are correct. That was exactly the problem. Thank you for your help!