Replace one formula with another in entire workbook

[Vivek D]

I have a user defined function that is used in hundreds of places all across a workbook.
The UDF is something like getRate("Some Text", SOME_CONSTANT). The function basically does a VLOOKUP into a table and gets the rate for the requested item. If it can't find the item then it returns zero.

I want to get rid of the UDF and just use a direct formula instead.

So getRate("Some Text", SOME_CONSTANT) needs to be changed to
IFERROR(VLOOKUP("Some Text", RATE_TABLE, SOME_CONSTANT, FALSE),0)

Note: The "Some Text" portion varies in each formula and SOME_CONSTANT is different too in some cases.

I basically need to do this to achieve what I need
1. Replace getRate( with IFERROR(VLOOKUP(
2. Replace SOME_CONSTANT with RATE_TABLE, SOME_CONSTANT, FALSE),0)

However, when I try the above, excel does not allow it as the formula becomes incorrect on doing the first replace. Doing the 2nd one first also gives an error.

I can probably break it down further and get it done but using too many replaces is a bit risky as it might affect some other formula.

Is there any other simple way to get this done?

 

[Sathish KV]

Hi,

Firtst replace all "=" sign with blank ""
then your can do your stuffs and then once again replace "IFERROR" with "=IFERROR"

 

[Luke M]

You were on the right track, but we need to make XL wait to calculate until we're done replacing. To do that, we'll purposely mess up the formula.

1. Replace =getRate( with IFERROR(VLOOKUP(
2. Replace SOME_CONSTANT with RATE_TABLE, SOME_CONSTANT, FALSE),0)
3. Replace IFERROR( with =IFERROR(

 

[Vivek D]

You were on the right track, but we need to make XL wait to calculate until we're done replacing. To do that, we'll purposely mess up the formula.

1. Replace =getRate( with IFERROR(VLOOKUP(
2. Replace SOME_CONSTANT with RATE_TABLE, SOME_CONSTANT, FALSE),0)
3. Replace IFERROR( with =IFERROR(

One additional problem though... getRate is not used exclusively within a cell formula, it could be part of a larger formula.
e.g. If (abc = 1, getRate(...), getRate(...))

 

[Vivek D]

Hi,

Firtst replace all "=" sign with blank ""
then your can do your stuffs and then once again replace "IFERROR" with "=IFERROR"

I have a lot of other formulas in the worksheet so don't want to mess up other stuff while trying to make this change. Replacing all "=" is therefore not an option and since there are hundreds of cells with the getRate UDF, selecting those specifically and changing is also a very cumbersome and time-consuming process.

 

[Luke M]

You should be able to run this macro to make all the switches. Note that there are a couple places for you to change the RATE_TAble and SOME_CONSTANT, if needed. Be careful of quotation marks.

Sub KillThemAll()
Dim myForm As String, newForm As String

'Change these next two as needed
Const part1 As String = "IFERROR(VLOOKUP("
Const part2 As String = "RATE_TABLE,SOME_CONSTANT,FALSE),0)"

Dim ws As Worksheet
Dim c As Range, formRange As Range

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Set formRange = Nothing
    On Error Resume Next
    Set formRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
   
    'Are there any cells with formulas?
    If formRange Is Nothing Then GoTo skipSheet
    Set c = Nothing
    With formRange
        Set c = .Find("getRate")
        'Are there any cells with this UDF?
        Do Until c Is Nothing
            myForm = c.Formula
            'Change this as needed
            newForm = Replace(Replace(myForm, "getRate(", part1), "SOME_CONSTANT)", part2)
            'Set the new formula
            c.Formula = newForm
            Set c = .Find("getRate")
        Loop
    End With
skipSheet:
Next ws
Application.ScreenUpdating = True
End Sub

 

[Vivek D]

You should be able to run this macro to make all the switches. Note that there are a couple places for you to change the RATE_TAble and SOME_CONSTANT, if needed. Be careful of quotation marks.

Sub KillThemAll()
Dim myForm As String, newForm As String

'Change these next two as needed
Const part1 As String = "IFERROR(VLOOKUP("
Const part2 As String = "RATE_TABLE,SOME_CONSTANT,FALSE),0)"

Dim ws As Worksheet
Dim c As Range, formRange As Range

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Set formRange = Nothing
    On Error Resume Next
    Set formRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
  
    'Are there any cells with formulas?
    If formRange Is Nothing Then GoTo skipSheet
    Set c = Nothing
    With formRange
        Set c = .Find("getRate")
        'Are there any cells with this UDF?
        Do Until c Is Nothing
            myForm = c.Formula
            'Change this as needed
            newForm = Replace(Replace(myForm, "getRate(", part1), "SOME_CONSTANT)", part2)
            'Set the new formula
            c.Formula = newForm
            Set c = .Find("getRate")
        Loop
    End With
skipSheet:
Next ws
Application.ScreenUpdating = True
End Sub

Thanks. This did it. No wonder you are an Excel Ninja

 

[Luke M]

Always glad to help. I enjoy a good challenge. Helps me keep my ninja samurai swords sharp.

 

[Vivek D]

Always glad to help. I enjoy a good challenge. Helps me keep my ninja samurai swords sharp.

I'll be sure to post a few more tricky challenges .