Groups > microsoft.public.excel.programming > #110064

Re: VLOOKUP formulas only set to values

From	Claus Busch <claus_busch@t-online.de>
Newsgroups	microsoft.public.excel.programming
Subject	Re: VLOOKUP formulas only set to values
Date	2017-06-08 18:08 +0200
Organization	A noiseless patient Spider
Message-ID	<ohbsj7$hpt$1@dont-email.me> (permalink)
References	<c6cb4145-a349-4897-8c8b-aae0deb1ae91@googlegroups.com> <ohaq3s$up2$1@dont-email.me> <d1e25160-f671-4126-a17e-e95ba1fc1d77@googlegroups.com> <ohb42f$rt9$1@dont-email.me> <fb2953a8-18d3-4d19-bbdf-a08ea8dbb4eb@googlegroups.com>

Show all headers | View raw

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.
-- 
Windows10
Office 2016

Thread

VLOOKUP formulas only set to values "L. Howard" <lhkittle@comcast.net> - 2017-06-07 16:51 -0700
  Re: VLOOKUP formulas only set to values Claus Busch <claus_busch@t-online.de> - 2017-06-08 08:20 +0200
    Re: VLOOKUP formulas only set to values "L. Howard" <lhkittle@comcast.net> - 2017-06-08 01:55 -0700
      Re: VLOOKUP formulas only set to values Claus Busch <claus_busch@t-online.de> - 2017-06-08 11:09 +0200
        Re: VLOOKUP formulas only set to values "L. Howard" <lhkittle@comcast.net> - 2017-06-08 08:13 -0700
          Re: VLOOKUP formulas only set to values Claus Busch <claus_busch@t-online.de> - 2017-06-08 18:08 +0200
            Re: VLOOKUP formulas only set to values "L. Howard" <lhkittle@comcast.net> - 2017-06-08 09:40 -0700

csiph-web