Excel - Changing a value using a look-up table

October 2016


I am looking for help on writing a macro that will perform a VLOOKUP formula that will compare the value in G2 on Sheet1, to the value in A1 on Sheet2. If the values match I would like to replace a value in G2 on Sheet1 with the value from B1 on Sheet2. The reason I need to replace the value in the same cell is because not every cell will have a match in the look-up table. If this is the case then the original value needs to stay.


G2 - Sheet1 A1 B1 - Sheet2 
12345 12345 ABCDE 
23456 34567 GHIJK 

G2 - Sheet1 A1 B1 - Sheet2 
23456 34567 GHIJK 

This is what I came up with so far but all I get is the value "0" in all of the cells.
ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[0]),WCSSCustItemNumLookupTable!R1C1:R2000C2,2,0)" 
Selection.AutoFill Destination:=Range("G2:G" + sPostDelLastRow) 


You can try some thing like this

Dim temp As Variant

    temp = "dummy value dude"
    On Error Resume Next
    temp = WorksheetFunction.VLookup(Range("G2"), Sheets("Sheet2").Range("A:B"), 2, False)
    On Error GoTo 0
    If temp <> "dummy value dude" Then
        Range("G2") = temp
    End If</code

Having said that, I think what you need to do is have another column for temp basis. Let say its column H on sheet 1

Do a lookup there


<code> Range("G2").Select

'doing a look up in temp range H
With Range("H2:H" & sPostDelLastRow)

'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup
.FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]),WCSSCustItemNumLookupTable!R1C1:R2000C2,2,0)), RC[-1], VLOOKUP(TRIM(RC[-1]),WCSSCustItemNumLookupTable!R1C1:R2000C2,2,0)"

.Copy 'copying

.PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula

.Copy 'copying again to move to actual G range

End With

'copy to G range
Range("G2:G" & sPostDelLastRow).PasteSpecial

'clear teamp range
Range("H2:H" & sPostDelLastRow).Clear


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Changing a value using a look-up table » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.