Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
VLOOKUP works but gets #REF when macro changes formulas to values | Excel Programming | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming | |||
vlookup formulas returning no values | Excel Worksheet Functions |