ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VLookup Vba¥Îªk

[µo°Ý] VLookup Vba¥Îªk

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-9-30 06:43 ½s¿è

·Q±N¤U¦Cªº¨ç¼Æ¤½¦¡¤Æ¬°µ{¦¡½X~
.[A4:A10].Formula = "=IF(A$1="""","""",VLOOKUP(A$1,DATA!$A:$H,ROW()-2,))": .[A4:A10] = .[A4:A10].Value    '¦C132
²{§ï¬°µ{¦¡½X
If .Cells(1, 1) <> "" Then .Range("A4:A10") = Application.VLookup(.Range("A1"), Sheets("DATA").Range("A:H"), Row() - 2, 0)    '¦C133

¦ý¦C133²£¥Í°»¿ù
¥¼©R¦W.png
2021-9-30 06:41

½Ð°Ý¡G¥¿½T»yªkÀ³¸Ó¦p¦ó½s¼g¡H
Àµ½Ð¦U¦ì¤j¤j½ç±Ð¡CÁÂÁ¡I

TEST_0930.rar (272.02 KB)

¦^´_ 1# ziv976688


If .[a1] <> "" Then For i = 4 To 10: .Cells(i, 1) = Application.VLookup(.Range("A1"), Sheets("DATA").Range("A:H"), i - 2, 0): Next

TOP

¦^´_ 1# ziv976688


.[A4:A10].Formula = "=IF(A$1="""","""",VLOOKUP(A$1,DATA!$A:$H,ROW()-2,))": .[A4:A10] = .[A4:A10].Value    '¦C132
>> ½Ð°Ý­ì¨Ó¨º­Ó'¦C132¤£¬O¥i¥H¥Î¶Ü? ¬°¦ó­n§ï?

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-9-30 14:15 ½s¿è

¦^´_ 2# samwang
´ú¸Õ¦¨¥\
¦h¾Ç¤@©Û(­ì¨ÓRow( )¡A¥²¶·¥H i = 4 To 10¨Óªí¹F)
ÁÂÁ±zªº«ü¾É~·P®¦

¦^´_ 3# samwang
¥u¬O·Q¾Ç²ß¨ç¼Æ¤½¦¡¦p¦óÂà¤Æ¬°µ{¦¡»yªk~
¥Ñ¨ç¼Æ¤½¦¡¤ñ¹ïµ{¦¡½X~¥½¾Ç¸û¯à®ø¤Æ»yªkªº·N¸q¡C
ÁÂÁ±z

TOP

¦^´_ 3# samwang
¤£¦n·N«ä~¥½¾Ç©|¦³3­Ó¨ç¼Æ¤½¦¡¡A·QÂà¤Æ¬°µ{¦¡»yªk~
¸Ô¦p½d¨Òªþ¥ó :   TEST_0930_V1.rar (279.76 KB)

¥H¤W   Àµ½Ð½ç±Ð¡CÁÂÁ±z¡I   

TOP

¦^´_ 5# ziv976688

¥½¾Ç©|¦³3­Ó¨ç¼Æ¤½¦¡¡A·QÂà¤Æ¬°µ{¦¡»yªk~
>> ¦pªþ¥ó¡A26¦Cªº³Ì¤p=0 ªº­Ó¼Æµª®×»P§Aªº®ÄªGÀɦ³®t²§¡A½Ð¦A½T»{±zªº±ø¥ó»Ý¨D¬°¦ó? ÁÂÁÂ

7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_1001.zip (37.55 KB)

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-2 00:38 ½s¿è

¦^´_ 6# samwang
¤£¦n·N«ä ¡A´ú¸Õ¥¼¹Fµ²ªG~
´ú¸Õ¦n´X¦¸¡A³£¦b   .EntireColumn.AutoFit    '¦C172³B¤¤Â_~¦p¹Ï¤ù
¥¼©R¦W.png
2021-10-2 00:05

¦pªG±N  .EntireColumn.AutoFit  §ï¬°µù¸Ñ«á~
¦AÂI°õ¦æ~¿Ã¹õ·|Åܶ«Ì~Àɮפ]Ãö¤£±¼~¥u¯à±j¨î­«·s¶}¾÷«á~¦A´ú¸Õ~´ú¸Õ¦n´X¦¸¡A³£¬O¦p¦¹

Àµ½Ð½ç¥¿
ÁÂÁ±z

TOP

¦^´_ 7# ziv976688


´ú¸Õ¦n´X¦¸¡A³£¦b   .EntireColumn.AutoFit    '¦C172³B¤¤Â_~¦p¹Ï¤ù
>> §Ú´ú¸Õ¨S°ÝÃD¡A½Ð¬Ýªþ¥ó¡A¥i¥H¥ý³]¤¤Â_ÂI¦b'¦C172ªº«e­±´X­Ó¡AµM«á°õ¦æµ{¦¡¨ì¤¤Â_ÂI«á¦A«öF8¥h°»´ú°£¿ù¡A
½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
1.gif

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-2 10:45 ½s¿è

¦^´_ 8# samwang
¤£¦n·N«ä¡A¨Ì·Ó±zªº»¡©ú¾Þ§@¡A«ö°õ¦æÁä«á~±o¦p¤U°»¿ùÂI¹Ï¤ù~
¥¼©R¦W.png
2021-10-2 10:23

¦A«öF8~¨S¦³¤ÏÀ³

¦]¬°¬Oªì¦¸¾Þ§@¦p¤Wªº¨BÆJ¡A§Ú¤]¤£ª¾¹D¨BÆJ¹ï¤£¹ï?§ó¤£ª¾¹D¦p¦ó³v¬q°»¿ù~
©Ò¥H°®¯Ü±N"ª©­±®æ¦¡"ªºµ{¦¡½X¥þ¬q§ï¬°µù¸Ñ~
¦A«ö°õ¦æÁä~¥i¥H¥¿±`¦Ó±o¨ìµ²ªG¤F¡C

´ú¸Õµ²ªG : W26¦³»~~µª®×À³¬° 1
¦]¬°W51= 0
¸Ô¦pªþ¥ó½d¨Ò : TEST_1002.rar (88.91 KB)

¥H¤W  Àµ½Ð½ç¥¿
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-2 19:58 ½s¿è

¦^´_ 8# samwang
½Ð°Ñ¦Ò¥»·sªþ¥ó½d¨Ò§Y¥i
TEST_1002_V2.rar (331.41 KB)
C26 : AY26ªº¸É¥R»¡©ú :
.[c26].Resize(1, 49)ªº·j´M½d³ò=.Range("c51:ay" & R)¡F²Î­p½d³ò=¥Ñc51°_©l¡A¨C¸õ17¦CªºÀx¦s®æ¡C

.[c26:ay26]Åã¥Üªº¼Æ¦r©Î¤å¦rªºµ{¦¡ÅÞ¿è~
¥H²Î­p½d³òªº¦U­Ó³æÄ欰¦U²Î­p¼Ðªº~
·í¬Y³æÄæ¥þ³¡=""®É¡A«h¸ÓÄ檺²Ä26¦CÀx¦s®æÅã¥Ü""¡C
EX1 : CÄ檺²Î­p½d³ò¥þ³¡=""¡F«hC26=""
EX2 : EÄ檺²Î­p½d³ò¥þ³¡=""¡F«hE26=""

¨ä¾l......Ãþ±À¡C

·í¬Y³æÄæ¥u­n¦³Åã¥Ü="0"ªº¼Æ¦r®É¡A«h±N¸ÓÄ榳Åã¥Ü="0"ªºÁ`­Ó¼Æ¡A¶ñ¤J²Ä26¦CÀx¦s®æ¡C
EX 1 : W Ä檺²Î­p½d³ò¤§W51¦³Åã¥Ü0¡F«hW26=1
EX 2 : ACÄ檺²Î­p½d³ò¤§AC85¦³Åã¥Ü0¡F«hAC26=1

·í¬Y³æÄæ³£¨S¦³Åã¥Ü="0"ªº¼Æ¦r¡F¦ý¦³Åã¥Ü¨ä¥¦>0ªº¼Æ¦r®É¡A«h¦b¸ÓÄæ²Ä26¦CÀx¦s®æ¡A¶ñ¤J"V"¡C
EX 1 : DÄæ³£¨S¦³Åã¥Ü="0"ªº¼Æ¦r¡F¦ýD136=2¡A«hD26=V
EX 2 : FÄæ³£¨S¦³Åã¥Ü="0"ªº¼Æ¦r¡F¦ýF119=1©MF136=2¡A«hF26=V
¨ä¾l......Ãþ±À¡C
¸Ô¦pªþ¥ó : 7T«e3¤j&¤p+0_0_1894´Á_100_6­Ó_1¦¸(»Ý¨D®ÄªGÀÉ)

¥Ø«e®ÄªGÀɪº W26=V~¨äµª®×¦³»~~
¦]¬°W51¦³Åã¥Ü0
©Ò¥HW26µª®×À³¬°1


¥H¤W   Àµ½Ð½ç¥¿
ÁÂÁ±z !

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD