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.
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
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
'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)"
.PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula
.Copy 'copying again to move to actual G range
'copy to G range
Range("G2:G" & sPostDelLastRow).PasteSpecial
'clear teamp range
Range("H2:H" & sPostDelLastRow).Clear