Excel - Modifier une valeur à l'aide d'une table de correspondance

Problème

Je cherche de l'aide sur l'écriture d'une macro qui effectuera une formule VLOOKUP qui comparera la valeur dans G2 sur Sheet1 à la valeur dans A1 sur Sheet2. Si les valeurs correspondent, je voudrais remplacer une valeur dans G2 sur Sheet1 par la valeur de B1 sur Sheet2. La raison pour laquelle j'ai besoin de remplacer la valeur dans la même cellule est parce que toutes les cellules n'auront pas de correspondance dans la table de correspondance. Si tel est le cas, la valeur d'origine doit rester.

Exemple...

Avant

 G2 - Feuille1 A1 B1 - Feuille2 12345 12345 ABCDE 23456 34567 GHIJK 34567

Après

 G2 - Feuille1 A1 B1 - Feuille2 ABCDE 12345 ABCDE 23456 34567 GHIJK GHIJK

C’est ce que j’ai trouvé jusqu’à présent, mais je n’obtiens que la valeur "0" dans toutes les cellules.

 Range ("G2"). Sélectionnez ActiveCell.FormulaR1C1 = "= VLOOKUP (TRIM (RC [0]), WCSSCustItemNumLookupTable! R1C1: R2000C2, 2, 0)" Selection.AutoFill Destination: = Range ("G2: G" + sPostDelLastR ) Plage ("F1"). Sélectionnez

Solution

Vous pouvez essayer quelque chose comme ça

 Dim temp As Variant temp = "valeur factice dude" On Error Relance Next temp = WorksheetFunction.VLookup (Range ("G2"), Feuilles ("Sheet2"). Range ("A: B"), 2, False) On Error GoTo 0 If temp "valeur indicative" alors Range ("G2") = temp End If 
Cela dit, je pense que ce que vous devez faire est d’avoir une autre colonne pour les postes temporaires. Laissons sa colonne H sur la feuille 1
Faire une recherche là
comme
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.
Article Précédent Article Suivant

Les Meilleurs Conseils