Excel - Menukar nilai menggunakan jadual paparan

Isu

Saya mencari bantuan untuk menulis makro yang akan melaksanakan formula VLOOKUP yang akan membandingkan nilai dalam G2 pada Sheet1, kepada nilai dalam A1 pada Sheet2. Jika nilai sepadan saya ingin mengganti nilai dalam G2 pada Sheet1 dengan nilai dari B1 pada Sheet2. Sebab saya perlu mengganti nilai dalam sel yang sama adalah kerana tidak setiap sel akan mempunyai perlawanan dalam jadual paparan. Sekiranya ini berlaku maka nilai asal perlu tinggal.

Contoh ...

Sebelum ini

 G2 - Sheet1 A1 B1 - Sheet2 12345 12345 ABCDE 23456 34567 GHIJK 34567

Selepas itu

 G2 - Sheet1 A1 B1 - Sheet2 ABCDE 12345 ABCDE 23456 34567 GHIJK GHIJK

Inilah yang saya datang sejauh ini tetapi semua yang saya peroleh adalah nilai "0" dalam semua sel.

 Pilih "ActiveCell.FormulaR1C1 =" = VLOOKUP (TRIM (RC [0]), WCSSCustItemNumLookupTable! R1C1: R2000C2, 2, 0) "Selection.AutoFill Destination: = Range (" G2: G "+ sPostDelLastRow ) Julat ("F1"). Pilih

Penyelesaian

Anda boleh mencuba beberapa perkara seperti ini

 Dim temp Sebagai Variant temp = "dude value dummy" On Ralat Resume Next temp = WorksheetFunction.VLookup (Range ("G2"), Sheets ("Sheet2") Range ("A: B"), 2, GoTo 0 Jika temp "dude nilai dummy" Kemudian Julat ("G2") = temp Akhir Jika 
Setelah mengatakan itu, saya fikir apa yang perlu anda lakukan adalah mempunyai satu lagi ruang untuk temp. Katakan lajur H pada helaian 1
Lakukan carian di sana
sebagai
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.
Artikel Sebelumnya Artikel Seterusnya

Tip-Tip Utama