Excel - Changing a value using a look-up table

December 2016




Issue


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.

Example...

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


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


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

Solution


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

as

<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

Note


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.