Excel - تغيير قيمة باستخدام جدول بحث

القضية

أنا أبحث عن مساعدة في كتابة ماكرو من شأنه أن يؤدي صيغة VLOOKUP التي تقارن القيمة في G2 على الورقة 1 ، بالقيمة الموجودة في A1 على الورقة 2. إذا كانت القيم متطابقة ، أود استبدال قيمة في G2 على الورقة 1 بالقيمة من B1 على الورقة 2. السبب في أنني بحاجة إلى استبدال القيمة في نفس الخلية هو أنه لن يكون لكل خلية تطابق في جدول البحث. إذا كان هذا هو الحال ، فإن القيمة الأصلية تحتاج إلى البقاء.

مثال...

قبل

 G2 - الورقة 1 A1 B1 - الورقة 2 12345 12345 ABCDE 23456 34567 GHIJK 34567 

بعد

 G2 - الورقة 1 A1 B1 - الورقة 2 ABCDE 12345 ABCDE 23456 34567 GHIJK GHIJK 

هذا ما توصلت إليه حتى الآن ولكن كل ما أحصل عليه هو القيمة "0" في جميع الخلايا.

 النطاق ("G2"). حدد ActiveCell.FormulaR1C1 = "= VLOOKUP (TRIM (RC [0]) ، WCSSCustItemNumLookupTable! R1C1: R2000C2،2،0)" Selection.AutoFill الوجهة: = Range ("G2: G" + sPostDelRow ) النطاق ("F1") 

حل

يمكنك تجربة بعض الشيء مثل هذا

 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 إذا temp "قيمة dude dude" ثم Range ("G2") = temp End If 
بعد قولي هذا ، أعتقد أن ما عليك فعله هو أن يكون لديك عمود آخر أساس مؤقت. دعنا نقول العمود H على الورقة 1
القيام بالبحث هناك
مثل
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.