Paste Value all the cells in a given sheet if Cell has Vlookup formula..

=VLOOKUP(VALUE($B$100);INDIRECT("2011.xls!"&D$101;0);3;0)

I have a sheet to send other people but i cannot send 2011.xls because it contains secret information. How can i copy-paste value all the cells in that sheet ("Centers") which has a formula like =VLOOKUP(VALUE($B$100);INDIRECT or contains VLOOKUP ?

Normally i would break link but because i have linked the table array with Indirect its not seen as a link

Answers


This procedure loops through all cells on the sheet and changes all VLOOKUP formulas to values:

Sub RemoveVlookupFormulas()
    Dim rng As Range
    With Worksheets("Centers")
        For Each rng In .UsedRange
            If rng.Formula Like "*VLOOKUP*" Then rng.Formula = rng.Value
        Next rng
    End With
End Sub

Need Your Help

Mac and Windows difference header path (/ vs \)

c++ visual-studio unix directory-structure windows

I have a Windows project that also works on XCode. But I had to changed all the source header paths like

Slow MySQL Updates/Inserts/Deletes

mysql performance

I seem to be having slow inserts, updates and deletes on all tables on a specific database with MySQL. Not a lot of data in those tables (from 2k to 20k). Small number of columns (5-10), indexes (t...