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?

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?

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?

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?