VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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

VLOOKUP formulas only set to values
 

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