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]

forgot to mention... using excel 2007

Thanks,
Joe

 

[Taul]

Hi,
This is not ideal but probably workable.

Use Find & Select
Go To Special
select "Formulas"
click ok
as you press the enter key, each formula in turn will become active
You will need to look at the formula shown and if it is a Vlookup, press F2 followed by F9 to calculate
if not simply press the enter key again to select the next formula.
This effectively means you are visually checking all the formulas one at a time, is this approach workable for you?

The alternative would be to use a macro to look through the sheet and evaluate the formula.

HTH
Paul.

 

[jbaich]

Hi Paul, thanks for the reply... actually a macro was route I was hoping to go... I just wasn't sure how to write it... I've recorded a "find and replace" macro that will take care of about 1/2 of them by replacing the old formula with a new formula that will correspond to the new sheet. I would rather replace with the formula result, but havent' been able go figure out how to do that yet... these first instances all have the same lookup reference (C12) in each workbook, but the rest rely on a lookup cell that is variable from sheet to sheet... In one workbook it might be C35 and in the next it might be C42.... Always C something though.... I was thinking if i could figure out how to find the cell address of the second instance of the heading "Roll Number" and then I would have the required row number to use in finding and replacing the rest...

Here's what's going on, maybe there's an entirely different solution that's easier and I'm not aware of it...

Currently a sheet (same sheet, different layout in many, many workbooks) has some vlookup formulas, but i need to delete the source sheet in each workbook and replace it with an updated version, which of course gives me the #REF! error and even if it didn't, the layout is slightly different so the lookups wouldn't return the correct the results anyways. I'd like to basically convert all vlookup formulas in each sheet to their value and number formats before i delete the source sheet and replace it. There are other formulas though that I want to leave in place, otherwise I'd just select it all and paste special as values and formats...

I'm just not sure how to look for a type of formula (or like a vlookup with wild card?) formula using vba and then replace the contents of the cell containing that formula with that formula's results...

Any advice would be greatly appreciated...

Thanks,
Joe

 

[jbaich]

can index and match be used to find formula's in cells and return the cell address? or could I make a loop using CELL("ADDRESS", and then something here to find the vlookup* and then use copy and past as values and formats for each go through the loop?

I think it's how to search for the vlookup formulas that is stumping me at the moment...

Thanks,
Joe

 

[jbaich]

ok, so i think i can use...

    Cells.Find(What:="vlookup*", After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

not sure how to set up the loop though... any suggestions?

Thanks,
Joe

 

[L. Howard]

Hi Joe,

See if this works for you.

Howard

Option Explicit

Sub VLOOKUP_to_VALUE()
   
    Dim FindV As String
    Dim RngD As Range
    Dim ws As Worksheet
    
    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

 

[jbaich]

Thanks for the reply Howard, I tried modifying your code slightly as I only want it to work on one worksheet in the workbook so i tried this...

Sub VLOOKUP_to_VALUE()   
    Dim FindV As String
    Dim RngD As Range
    Dim ws As Worksheet
    
    
    FindV = "VLOOKUP"




    Set ws = ActiveWorkbook.Sheets("Valuation Worksheet")
    
      With ws.UsedRange
            
           Set RngD = .Find((FindV), , xlFormulas, xlPart)


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


        End With


      


      
End Sub

It seems to set all formulas to their values... when I hover over the RngD variable after the Set command, it shows Rngd = Error 2042, which from what google tells me is basically "no match"... is UsedRange compatible with 2007? I tried setting a specific range with

    Set ws = ActiveWorkbook.Sheets("Valuation Worksheet")    
      With ws.Range("A1:Q100")
            
           Set RngD = .Find((FindV), , xlFormulas, xlPart)

But no luck...

is it that it's not finding matches to VLOOKUP or it's not recognizing the "range"?

Thanks,
Joe

 

[L. Howard]

Hmmm, I did not have other formulas on my test sheet. With other formulas it does indeed convert all to values.

I'll have to ponder that as to why.

Howard

 

[L. Howard]

Try it like this. For one sheet or the workbook, all sheets.
(In a standard module)
With thanks to Claus @MSPublic

Howard

Option Explicit

Sub VLOOKUP_to_VALUE_SHEET()
Dim wsh As Worksheet
Dim myRng As Range, rngC As Range
Dim strRng As String
   strRng = ""
   With ActiveWorkbook.Sheets("Valuation Worksheet")
      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
      
      On Error Resume Next
      With .Range(Mid(strRng, 2))
         .Value = .Value
      End With
   End With
End Sub

Sub VLOOKUP_to_VALUE_WBOOK()
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
      
      On Error Resume Next
      With .Range(Mid(strRng, 2))
         .Value = .Value
      End With
   End With
Next
End Sub