Excel - Changing a value using a look-up table

February 2017




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


Published by aakai1056. Latest update on April 7, 2010 at 09:44 AM by aakai1056.
This document, titled "Excel - Changing a value using a look-up table," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).