Macro to paste special value only VLOOKUP formulas

J

jeremy nickels

I need a macro that will find all the cells in a worksheet that have a
VLOOKUP formula and do a copy, paste special values on those cells. There
are other formulas (SUM, IF, etc.) in the workbook, but I need to leave those
intact. Can anyone help?
 
T

Tom Ogilvy

Sub AB()

Set rng = Cells.Find(What:="Vlookup", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
Do
rng.Formula = rng.Value
Set rng = Cells.FindNext(rng)
Loop Until rng Is Nothing
End If
End Sub
 
J

jeremy nickels

Thanks Tom, that works great!

Tom Ogilvy said:
Sub AB()

Set rng = Cells.Find(What:="Vlookup", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
Do
rng.Formula = rng.Value
Set rng = Cells.FindNext(rng)
Loop Until rng Is Nothing
End If
End Sub
Click to expand...
 
A

Abid

Hi,

This was very useful for me as well. Could you please also help with
extending this to a scenario where same function needs to be performed on all
worksheet in the workbook?

Thanks
SA
 
Top