Replacing anything after the last parenthetical in a formula

[jomili]

I have a cost template that I have to use. It's made by one group, completed by another, and sent to me for final touches. In the original template, there's a tab with about 5000 VLookups, like this:

=ROUND(VLOOKUP($D549,'MOF Data Amounts'!$A$6:$G$132,3,FALSE)*$G549,0)

To make fine adjustments, the second group adds or subtacts a one decimal number (1-9) to the end of the formula. Sometimes they do it a couple of times, like so:

=ROUND(VLOOKUP($D570,'MOF Data Amounts'!$A$6:$G$132,3,FALSE)*$G570,0)+1-0.14

As is usual in this type of circumstance, group 2 sometimes fouls it up, so I want to revert that one tab back to it's original. I can't just refresh the formulas, as there are DIFFERING Vlookups in most cells/rows. What I usually do is a Find/Replace, and replace "0)-1" with "0)". However, changes such as the one above throw me off. I'd like to be able to just wipe out everything after the last pareenthetical. Any ideas?

[Paul]

Hi Jomili,

The following macro should remove everything after the last parenthesis in all cells with VLOOKUP formulas:

Sub removeStuff()
Dim c As Range
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    If InStr(1, c.Formula, "VLOOKUP") > 0 Then _
        c.Formula = Left(c.Formula, InStrRev(c.Formula, ")"))
Next c
End Sub

If you have two VLOOKUP's in one cell it would only remove the stuff after the last paren's not the first set, e.g.

=VLOOKUP("abc",$A$1:$B$10,2,0)+1-0.14+VLOOKUP("def",$A$1:$B$10,2,0)+1-0.43

would just become:

=VLOOKUP("abc",$A$1:$B$10,2,0)+1-0.14+VLOOKUP("def",$A$1:$B$10,2,0)

[jomili]

Paul, it looks like it worked, but VERY slowly. I have to leave for the day so can't fully examine the results, but will try to test it again in the morning, turning screen updating off to see if that speeds it up. FYI, my sheet has about 750 rows, with 15 columns of VLookups, most preceeded by a ROUND or IFERROR.

[Paul]

I'd suggest also turning calculation to manual mode in the code, and then setting it back at the end of the code (if it's currently Automatic).

[jomili]

That's a good thought. I think I'll also change it to be by selection rather than the used range, as I could see using it on other sheets and might not want to clean all formulas on the sheet. I won't have time to fool with it this morning, but will try to post the updated macro later today if I can. Thanks for all your help!

[jomili]

Paul,

Here's the updated macro. Still extremely slow, UNLESS I only select the formula range in one column at a time. Then it goes pretty quick. If you have any ideas for speeding it up I'm all ears.

Sub RemoveAfterVLookup()
Application.ScreenUpdating = False
Dim xlCalc As XlCalculation

    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack

Dim c As Range
For Each c In Selection.SpecialCells(xlCellTypeFormulas, 23)
    If InStr(1, c.Formula, "VLOOKUP") > 0 Then _
        c.Formula = Left(c.Formula, InStrRev(c.Formula, ")"))
Next c

Application.Calculation = xlCalc
Application.ScreenUpdating = True

    Exit Sub

CalcBack:
Application.Calculation = xlCalc
Application.ScreenUpdating = True

End Sub

[Paul]

I'm surprised that it's not working very well for you. I just tried on my PC (Win7/Excel2013) with a sheet containing about 5000 VLOOKUP formulas and another 5000+ text cells over 100+ columns and 100+ rows. I did the entire UsedRange and added a Timer to see how long it takes, and the worst I saw was 3.06 seconds to do all of the replacements and re-calc.

Sub RemoveAfterVLookup()
Application.ScreenUpdating = False
Dim xlCalc As XlCalculation, StartTime, EndTime, TotalTime

    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack

Dim c As Range
StartTime = Timer
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    If InStr(1, c.Formula, "VLOOKUP") > 0 Then _
        c.Formula = Left(c.Formula, InStrRev(c.Formula, ")"))
Next c
EndTime = Timer
TotalTime = EndTime - StartTime
ActiveSheet.Range("A15").Value = TotalTime
Application.Calculation = xlCalc
Application.ScreenUpdating = True

    Exit Sub

CalcBack:
Application.Calculation = xlCalc
Application.ScreenUpdating = True

End Sub

[jomili]

I've had it running for over 5 minutes now, and it still hasn't stopped. The WB is a 2010 WB, weighing 6MB, with 15 tabs, 20 pivot tables, tons of various formulas. The worksheet is as I stated earlier.

[Paul]

Yikes. I'm guessing other data and/or pivot tables are dependent upon those VLOOKUP's. Perhaps there's a better way using arrays or regex functions, but I'm not the person for those methods.

Is the UsedRange much larger than the actual used area of the worksheet? How many other formulas are in the worksheet that it is checking?

[jomili]

I'm so sorry, I just noticed I never answered your questions. Excel thinks the UsedRange is A1:AA813, but the real used range is B1:AA726. So, not a huge disconnect.

Every cell has a formula, so there are a ton of them.