Excel - Een waarde wijzigen met behulp van een opzoektabel

Kwestie

Ik ben op zoek naar hulp bij het schrijven van een macro die een VERT.ZOEKEN-formule zal uitvoeren die de waarde in G2 op Blad1 zal vergelijken met de waarde in A1 op Blad2. Als de waarden overeenkomen, wil ik een waarde in G2 op Sheet1 vervangen door de waarde van B1 op Sheet2. De reden dat ik de waarde in dezelfde cel moet vervangen, is omdat niet elke cel een overeenkomst heeft in de zoektabel. Als dit het geval is, moet de oorspronkelijke waarde blijven.

Voorbeeld...

Voor

 G2 - Blad1 A1 B1 - Blad2 12345 12345 ABCDE 23456 34567 GHIJK 34567

 G2 - Blad1 A1 B1 - Blad2 ABCDE 12345 ABCDE 23456 34567 GHIJK GHIJK

Dit is wat ik tot nu toe bedacht heb, maar alles wat ik krijg is de waarde "0" in alle cellen.

 Bereik ("G2") Selecteer ActiveCell.FormulaR1C1 = "= VLOOKUP (TRIM (RC [0]), WCSSCustItemNumLookupTable! R1C1: R2000C2, 2, 0)" Selection.AutoFill Bestemming: = Bereik ("G2: G" + sPostDelLastRow ) Bereik ("F1"). Selecteer

Oplossing

Je kunt zoiets proberen

 Dim temp As Variant temp = "dummy value kerel" On Error Resume Next temp = WorksheetFunction.VLookup (Range ("G2"), Sheets ("Sheet2"). Bereik ("A: B"), 2, False) On Error GoTo 0 Als temp "dummy value kerel" Dan bereik ("G2") = temp Einde indien 
Dat gezegd hebbende, denk ik dat je een andere kolom voor tijdelijke basis moet hebben. Laat haar kolom H op blad 1 zeggen
Kijk daar eens
zoals
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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. 
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.