Thread: Finding All VLOOKUPs and Replacing Them With The Cell's Value

Hi there, I am a first time poster. Hopefully I am in the correct section.

I am working with a bunch of spreadsheets that contain several cells with mathematical formulas (for example, =IF(W43>0,W43/$W$48,"")) as well as VLOOKUPs. Long story short, I need to keep the mathematical formulas and "get rid" of the VLOOKUPs. By "get rid", I mean I need to find all the cases where VLOOKUP is used, then replace each case with the cell's numerical or text value. The tedious way of doing this would be finding each case, copying the cell, and pasting as values.

To illustrate, a cell contains =VLOOKUP(etc, etc, etc...) and the end result of this has the cell displaying "Public Pension". I need to change it so that the cell's contents to simply contains the text value "Public Pension".

Is there any way of doing this using macros? I have 150 spreadsheets and each has about 15 cases of VLOOKUP.

Once again, I hope I am in the right section and that I am explaining clearly. Thank you very much.

wow, thank you so much! quick and accurate

Hi tigeravatar - I'm also a first-time poster, and have a similar question. I consider myself an intermediate Excel 2007 user (I know a lot of formulae but don't even know how to use VBA - I'm taking a class in a few weeks.)

In my case, rather than replace the VLOOKUP with the value that's ultimately output, can I replace it with a link to the cell that the VLOOKUP ultimately references to? I have literally hundreds of VLOOKUPs in a 10 MB workbook that I'd like to replace with direct links to improve performance. Often there are many VLOOKUPs in a single cell, and I'd like to replace all of them with direct cell references without losing the rest of the operations in the cell.

Your code is as close as I've seen to a solution - hopefully there's any easy way to replace VLOOKUPs with links instead of text!

Originally Posted by tigeravatar

venny,

Welcome to the forum. The following macro loops through each sheet in the activeworkbook and replaces all formulas containing vlookup with its resulting value:

Sub ReplaceVlookupsWithValuesMacro_for_venny()
    
    Dim ws As Worksheet
    Dim FormulaCell As Range, rngVLKP As Range
    
    For Each ws In ActiveWorkbook.Sheets
        Set rngVLKP = Nothing
        For Each FormulaCell In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
            If InStr(1, FormulaCell.Formula, "VLOOKUP(", vbTextCompare) > 0 Then
                If rngVLKP Is Nothing Then
                    Set rngVLKP = FormulaCell
                Else
                    Set rngVLKP = Application.Union(rngVLKP, FormulaCell)
                End If
            End If
        Next FormulaCell
        If Not rngVLKP Is Nothing Then rngVLKP.Value = rngVLKP.Value
    Next ws
    
End Sub

Hope that helps,
~tigeravatar