Changing a value using a look-up table

froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 - Apr 2, 2010 at 06:45 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 6, 2010 at 11:18 AM

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)

Thank you in advance for any help,



1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 6, 2010 at 11:18 AM
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


<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