Urgent Help Required! Double string macro to remove the 1st & last part of Forumla

If you remove it and drag down, even if it contains 20 different tabs, wont it work?

Also, should this 1 formula be edited for all 3000 cells? Are those 3000 cells all in 1 column?

Instead of a .jpg, please attach a sample excel file. It will help us to work out a faster solution for you.

Try this on a copy of your workbook as the results CANNOT be undone.

Sub test ()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Dim sh As Integer, arrCol, x As Integer, c As Range
    For sh = 1 To Sheets.Count
        arrCol = [{"A","B","C","D","E","F","G","H","I","J","K","L"}]
        For x = LBound(arrCol) To UBound(arrCol)
            For Each c In Sheets(sh).UsedRange
                c.Replace What:="IF(" & arrCol(x) & "$5=""CurFcstBudRate"",", Replacement:=""
                c.Replace What:=","""")", Replacement:=""
            Next
        Next
    Next

Application.Calculation = xlAutomatic
End Sub

Just tested on your sample and seems to work fine even though it takes a few minutes to run. I have added a few extra lines above to speed things up a bit. Will check back in a few hours to see if it worked for you.

My mistake. I forgot to paste the first line. Add the line:

Sub test ()

at the beginning to the code.

I am rerunning it from work with a few tweaks. Hopefully will get back to you soon.

abousetta

Hi Chris,

OK, I think this should work now. I tested on a small sample because it keeps asking me for the location of the add-in you have on your system. So I didn't get a chance to run it on the whole workbook, but let me know if still is not working.

abousetta

Option Explicit
Option Base 1

Sub test2()

  With Application
      .ScreenUpdating = False
      .DisplayStatusBar = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
  End With

Dim sh As Integer, arrCol, x As Integer, c As Range
    For sh = 1 To Sheets.Count
        arrCol = [{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R"}]
        For Each c In Sheets(sh).UsedRange
            If c.HasFormula Then
                If Mid(c.Formula, 10, 14) = "CurFcstBudRate" Then
                    c.Formula = "=" & Mid(c.Formula, 26, Len(c.Formula) - 29)
                End If
            End If
        Next
    Next

  With Application
      .DisplayStatusBar = True
      .EnableEvents = True
      .Calculation = xlAutomatic
  End With
End Sub

Have a look at the attachment. Maybe I didn't understand what you were trying to remove correctly.

abousetta

Thanks bstubbs. I turned off link updating and that allowed the formulas to be updated. Thanks for the headsup and I will keep this in mind.

Glad it all worked out.

Good luck.

abousetta