Replace Text in Formula VBA

campbesj7

New Member
Joined
Feb 25, 2016
Messages
8
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Range.Replace is looking for values not formulas. Try this:
Code:
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

New Member
Joined
Feb 25, 2016
Messages
8
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

New Member
Joined
Feb 25, 2016
Messages
8
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,961
Members
414,114
Latest member
Lost_User21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top