Hi Howard,

Am Wed, 7 Jun 2017 16:51:04 -0700 (PDT) schrieb L. Howard:

The need is to change the existing worksheet vlookup returns to a value.
(And/or many worksheets)

The Vlookup formulas are the only targets I want to change to values.
A specific range on the sheet is okay instead of UsedRange.

This does set Vlookup formulas to values, but it also sets all other formulas to values.

try:

Sub Test()
Dim wsh As Worksheet
Dim myRng As Range, rngC As Range
Dim strRng As String

For Each wsh In Worksheets
strRng = ""
With wsh
Set myRng = .UsedRange.SpecialCells(xlCellTypeFormulas)
For Each rngC In myRng
If InStr(rngC.Formula, "VLOOKUP") Then
strRng = strRng & "," & rngC.Address(0, 0)
End If
Next
With .Range(Mid(strRng, 2))
.Value = .Value
End With
End With
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016