Macro to paste special value only VLOOKUP formulas
 

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?

Macro to paste special value only VLOOKUP formulas
 

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

--
Regards,
Tom Ogilvy

"jeremy nickels" wrote in message
...
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?

Macro to paste special value only VLOOKUP formulas
 

Thanks Tom, that works great!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"jeremy nickels" wrote in message
...
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?

Macro to paste special value only VLOOKUP formulas
 

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


"jeremy nickels" wrote:

Thanks Tom, that works great!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"jeremy nickels" wrote in message
...
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?