Changing a value using a look-up table

Solved/Closed
Report
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

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

Thank you in advance for any help,

Matt


1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you sure this is right Range("G2:G" + sPostDelLastRow) . I am not sure about the + sign, I thought it has to be an & to concatenate

Also I see one more possible issue, You are assigning the formula to G2 for lookup, What if lookup does not return any value.

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
    


I have not tested it so there might be some errors. But I am sure you get the idea