Is it possible to replace vlookup formulas only with their resulting values?

[jbaich]

Basically is it possible to find and replace vlookup formulas in a sheet and replace with their resulting values without impacting any of the other formulas in the sheet? Like a find and replace if formula contains vlookup with that cells value?

Thanks,
Joe

 

[jbaich]

Thanks Howard and Clause @MSPublic, it seems pretty close to working, it finds all the cell addresses with Vlookup formulas, but rather than replacing each formula with it's result, it replaces all of the formulas with the same value... not sure if it's the first or last vlookup value or what...

so after the loop, strRng : ",N15,G15,G16,G17,N18,N19,N20,N21,N22,N23,N42,N46,N47,N48" : String

Then it looks like

with.Range(Mid(strRng,2))

is maybe just taking the result in a single one of these cells... either N15 or N42 as they both return the same result, and replacing all the cells in the range with this value.

Seems like maybe I need to make another loop? for each address in strRng .Value = .Value...?

Or could the existing loop be modified... maybe by replacing

If InStr(rngC.Formula, "VLOOKUP") Then
            strRng = strRng & "," & rngC.Address(0, 0)

with something like

If InStr(rngC.Formula, "VLOOKUP") Then
            rngC.Address(0, 0) = .Value
End If

Does that make sense?

Thanks,
Joe

 

[jbaich]

nevermind, I figured it out!

      For Each rngC In myRng         If InStr(rngC.Formula, "VLOOKUP") Then
            strRng = rngC.Address(0, 0)
            With .Range(strRng)
            .Value = .Value
            End With
         End If
      Next

I changed

[COLOR=#333333] strRng = strRng & "," & rngC.Address(0, 0)[/COLOR]

to

[/COLOR]strRng = rngC.Address(0, 0)[COLOR=#333333]

and then

[COLOR=#333333]with.Range(Mid(strRng,2))[/COLOR]

to

With .Range(strRng)

and it seems to work great!

Thanks for all the help!!!

 

[jbaich]

So I actually have one more quick question if that's alright... I've never really understood this and despite having researched and asked people, I still mostly don't, but I'd like to call this sub from another sub and I'd like to pass the name of the workbook this sub is to operate on from the previous sub... But I just can't seem to figure out (and understand) what is required to do that...

I want to do

Call VLOOKUP_to_VALUE_SHEET(wbDest)

where wbDest is the destination workbook i want the code to run in...

With wbDest.Sheets("Valuation Worksheet")

I've tried declaring Public wbDest as String and wbDest as Workbook... not sure which it's supposed to be as I think i'm just needing to pass the name of wbk?

Then in the sub I've tried Sub VLOOKUP_to_VALUE_SHEET(wbDest As String) and as workbook, but I keep getting the compile error ByRef argument type mismatch... I've tried Sub VLOOKUP_to_VALUE_SHEET(ByRef wbDest As String) and as workbook, but no luck...

I don't know why I'm having such a hard time understanding how this works, but it just doesn't seem to compute with me... I would really appreciate it if you could explain how to call this sub and pass along the wbDest variable and why it's done that way?

Thanks,
Joe

 

[L. Howard]

Hi Joe,

Here is a revised set of macros for sheet and workbook. Sorry I did not catch the code flaw in previous codes posted.

Regarding passing the workbook name to a subroutine, there are many examples posted to do such. I look at them and am also confused how to actually make it happen.

I suggest you do a separate new post with "pass the name of the workbook to subroutine..." as the subject title. Include the VLOOKUP_to_VALUE_SHEET code and the "other code" along with the name of the destination workbook.
Then the solution will be using your exact examples.

Howard

Option Explicit
Sub VLOOKUP_to_VALUE_WBOOK()
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

Sub VLOOKUP_to_VALUE_SHEET()
Dim myRng As Range, rngC As Range

      With ActiveWorkbook.Sheets("Valuation Worksheet")

      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
End Sub

 

[Taul]

Hi,
I found this on another site, looks like it would also do the job.
put it in a standard module

Sub RemoveVlookupFormulas()
    Dim rng As Range
    'With Worksheets("Data")
     With ActiveSheet
        For Each rng In .UsedRange
            If rng.Formula Like "*VLOOKUP*" Then rng.Formula = rng.Value
        Next rng
    End With
End Sub

source Paste Value Cell if Cell has Vlookup formula..