Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: VLOOKUP formulas only set to values

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

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