On Thursday, June 8, 2017 at 9:08:34 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Thu, 8 Jun 2017 08:13:22 -0700 (PDT) schrieb L. Howard:

I just noticed that the returns are all changed to the same value.
I was looking only at the cells to see if the formula was gone, which it had, but the results are identical for all the cells with vlookup.

sorry, my bad.
Try:

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

For Each wsh In Worksheets
With wsh
If .UsedRange.SpecialCells(xlCellTypeConstants).Count = _
.UsedRange.Cells.Count Then GoTo NextSheet
Set myRng = .UsedRange.SpecialCells(xlCellTypeFormulas)
For Each rngC In myRng
If InStr(rngC.Formula, "VLOOKUP") Then
With rngC
.Value = .Value
End With
End If
Next
End With
NextSheet:
Next
End Sub



Regards
Claus B.
--

Hi Claus,
Good fix, that does it.

Thanks much.

Howard