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

Groups > microsoft.public.excel.programming > #110059 > unrolled thread

VLOOKUP formulas only set to values

Started by"L. Howard" <lhkittle@comcast.net>
First post2017-06-07 16:51 -0700
Last post2017-06-08 09:40 -0700
Articles 7 — 2 participants

Back to article view | Back to microsoft.public.excel.programming

Contents

  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

#110059 — VLOOKUP formulas only set to values

From"L. Howard" <lhkittle@comcast.net>
Date2017-06-07 16:51 -0700
SubjectVLOOKUP formulas only set to values
Message-ID<c6cb4145-a349-4897-8c8b-aae0deb1ae91@googlegroups.com>
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.

Thanks.
Howard


Sub VLOOKUP_to_VALUE()
   
    Dim FindV As String
    Dim RngD As Range
    Dim ws As Worksheet
    Dim LRow As Long

    FindV = "VLOOKUP"

    For Each ws In ThisWorkbook.Worksheets

      With ws.UsedRange
            
           Set RngD = .Find((FindV), , xlFormulas, xlPart)

              If Not RngD Is Nothing Then
                .Value = .Value
               End If

        End With

      Next ws
      
End Sub

[toc] | [next] | [standalone]

#110060

FromClaus Busch <claus_busch@t-online.de>
Date2017-06-08 08:20 +0200
Message-ID<ohaq3s$up2$1@dont-email.me>
In reply to#110059
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

[toc] | [prev] | [next] | [standalone]

#110061

From"L. Howard" <lhkittle@comcast.net>
Date2017-06-08 01:55 -0700
Message-ID<d1e25160-f671-4126-a17e-e95ba1fc1d77@googlegroups.com>
In reply to#110060
On Wednesday, June 7, 2017 at 11:20:06 PM UTC-7, Claus Busch wrote:
> 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.

Hi Claus,

Works great. It did not occur to me to use InStr.  But I would not have gotten the syntax correct either.

I added...
 
      On Error Resume Next
      With .Range(Mid(strRng, 2))
         .Value = .Value
      End With

It errors if there are no VLOOKUP's on the sheet.

Thanks much.

Howard

[toc] | [prev] | [next] | [standalone]

#110062

FromClaus Busch <claus_busch@t-online.de>
Date2017-06-08 11:09 +0200
Message-ID<ohb42f$rt9$1@dont-email.me>
In reply to#110061
Hi Howard,

Am Thu, 8 Jun 2017 01:55:18 -0700 (PDT) schrieb L. Howard:

> It errors if there are no VLOOKUP's on the sheet.

check strRng for <>"":

If strRng <> "" Then
         With .Range(Mid(strRng, 2))
            .Value = .Value
         End With
End If


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#110063

From"L. Howard" <lhkittle@comcast.net>
Date2017-06-08 08:13 -0700
Message-ID<fb2953a8-18d3-4d19-bbdf-a08ea8dbb4eb@googlegroups.com>
In reply to#110062
On Thursday, June 8, 2017 at 2:10:03 AM UTC-7, Claus Busch wrote:
> Hi Howard,
> 
> Am Thu, 8 Jun 2017 01:55:18 -0700 (PDT) schrieb L. Howard:
> 
> > It errors if there are no VLOOKUP's on the sheet.
> 
> check strRng for <>"":
> 
> If strRng <> "" Then
>          With .Range(Mid(strRng, 2))
>             .Value = .Value
>          End With
> End If
> 
> 
> Regards
> Claus B.


Hi Claus,

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.

Howard

[toc] | [prev] | [next] | [standalone]

#110064

FromClaus Busch <claus_busch@t-online.de>
Date2017-06-08 18:08 +0200
Message-ID<ohbsj7$hpt$1@dont-email.me>
In reply to#110063
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

[toc] | [prev] | [next] | [standalone]

#110065

From"L. Howard" <lhkittle@comcast.net>
Date2017-06-08 09:40 -0700
Message-ID<0fde60de-365d-4787-9fae-6def56189dd7@googlegroups.com>
In reply to#110064
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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web