Now that I understand the last parameter in VLOOKUP(), i now have thousands of problems

[vanagas]

Team:
The last parameter in VLOOKUP() essentially tells EXCEL whether the range being used for lookup is in ASCII order or not. If not, it has to plow through all the entries in the range, as opposed to doing a binary "search all" (of COBOL fame).
So not understanding this for a long time, most all my VLOOKUP() functions are using TRUE as the last parm. And there are literally thousands in this spreadsheet.

So..... here it comes.....
How do I do a mass change to change the last parameter in all my VLOOKUP() functions to FALSE? Is it even possible, without going into every single cell?
And with copy/paste misbehaving VERY BADLY, this becomes ever more complicated.

Using Win10, Excel 2019 (have access to Excel 2013, 2009......)
Thanks for any ideas....

 

[lrobbo314]

You could select the cells that have the formulas in them and use find and replace, ctrl+H, to replace True with False.

 

[vanagas]

You could select the cells that have the formulas in them and use find and replace, ctrl+H, to replace True with False.

Wait a sec..... you are suggesting i do a global replace changing all occurrences of "1" with a "0"?????
Somehow, i think that will likely have a few unintended consequences.
In 4 sheets of a workbook (with about 240 sheets), i have counted 212 VLOOKUP(). In JUST 4 sheets!!!!
Any other ideas?

 

[lrobbo314]

Are the formulas just vlookup, or are they combined with other formulas. e.g. vlookup(value, table, column, exact) as opposed to vlookup(if(... etc?

 

[bmacias]

How about if when you try find and replace, ctl+H, instead of replacing just the "1" with a "0" you replace the ",1)" with ",0)"? That would curtail severely any unintended consequences. If the vlookup "table" is the same through out your worksheet formulas you could even expand the find and replace even further.

 

[vanagas]

Are the formulas just vlookup, or are they combined with other formulas. e.g. vlookup(value, table, column, exact) as opposed to vlookup(if(... etc?

Unfortunately, they are all mixed in.
Some Examples:

=MIN(HLOOKUP(VLOOKUP($C$2,Abbrev!$A$1:$Z$51,5,0),$AZ$27:$BH$48,22,0),VLOOKUP($C$2,Abbrev!$A$1:$Z$51,23,0))
=IF(ISBLANK($I29),0,VLOOKUP($I29,Components!$B$3:$CE$1148,AA$5,0)*$S29/SUM($S$28:$S$35))
=IF(LEFT($I28,1)<>"F","",VLOOKUP($I28,Components!$B$3:$CE$1148,BB$5,0))
=IF(VLOOKUP($C$2,Abbrev!$A$2:$Z$51,7,0),SUM(T28:T46),SUM(T22:T46))

 

[DanteAmor]

Try the following macro, replace "1" with "0" only from the VLOOKUP formulas.
You can have a VLOOKUP or several VLOOKUP in the same formula and only replace the 1 by 0 of the VLOOKUP

Sub reemp()
    Dim s As Worksheet, c As Range
    Dim cuenta As Long, ini As Long, i As Long, n As Long, m As Long, o As Long
    Set s = ActiveSheet
    For Each s In Sheets
        For Each c In s.Cells.SpecialCells(xlCellTypeFormulas)
            If InStr(1, c.Formula, "VLOOKUP") Then
                cuenta = (Len(c.Formula) - Len(WorksheetFunction.Substitute(c.Formula, "VLOOKUP", ""))) / 7
                ini = 1
                For i = 1 To cuenta
                    n = InStr(ini, c.Formula, "VLOOKUP")
                    m = InStr(n, c.Formula, ")")
                    o = InStr(n, c.Formula, "1)")
                    If o >= n And o <= m Then
                        c.Formula = WorksheetFunction.Replace(c.Formula, o, 1, "0")
                    End If
                    ini = n + 1
                Next
            End If
        Next
    Next
End Sub