ªð¦^¦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)

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ 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

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

¦^´_ 8# samwang
°Ñ¦Ò   http://forum.twbts.com/thread-23380-1-1.html   8#ªº¶Q¸Ñ~
±NIf Arr(i, j) = 0 Then n = n + 1: Arr(1, j) = n Else Arr(1, j) = "V": GoTo 100]    '¦C167
§ï¬°
    If Arr(i, j) = 0 Then n = n + 1    '¦C167
    If n > 0 Then Arr(1, j) = n Else Arr(1, j) = "V": GoTo 100    '¦C168
¶]¥X¨Óªºµª®×¬O¥¿½Tªº¡C
¦ý¤£ª¾¹D³o¼Ë§ï¹ï¤£¹ï ? (¨S¦³¦Û«H)

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

TOP

¦^´_ 12# samwang
OK¤F
ÁÂÁ±zªº­@¤ß«ü¾É©M¼ö¤ßÀ°¦£~¨ü¯q¨}¦h~·P®¦

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-4 07:11 ½s¿è

¦^´_ 12# samwang
TEST_1004.rar (325.22 KB)
¤£¦n·N«ä¡A³Ì«á2­Ó°ÝÃD~³Ò¾r±z½ç¥¿~ÁÂÁ±z

°ÝÃD1¡G
7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_1003(C2_C26=VBA)°õ¦æ«áªº®ÄªGÀÉ~
²Î­p"0"ªº­Ó¼Æ~¦bC26³£·|©T©w¦h1­Ó(¥H7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_0930(C2_C26=fx)ªº®ÄªGÀɤñ¹ï)~
½Ð¸Ô¨£¡G7T«e3¤j&¤p_0_1902´Á_100_7­Ó_1¦¸(C26=VBA®ÄªGÀÉ)¡F7T«e3¤j&¤p_0_1902´Á_100_14­Ó_1¦¸(C26=VBA®ÄªGÀÉ)
PS¡G¦pªG±z­n´ú¸Õ14­ÓÀɮתº¸Ü~½Ð±N¥t7­Ó7RA²ÎªºÀɮסA¸m¤J¤l¸ê®Æ§¨¤º«á~¦A°õ¦æ(¥DÀÉ)§Y¥i¡C

°ÝÃD2¡G
7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_1003(C2_C26=VBA)ªº¦C191~¦C197µ{¦¡½X¤£¯à°õ¦æ~
¦pªG°õ¦æ~·|¦b¦C195¦Û°ÊÄæ¼e²£¥Í"°»¿ù"~µM«áµLªkÃö³¬ÀÉ®×~·|·í¾÷~¥²¶·±j¨î­«·s¶}¾÷

³Æµù¡G
¦pªG±N7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_1003(C2_C26=VBA)ªº
¦C3§ï¬°
Dim Path As String, A, Ar(1 To 1000, 1 To 2), Arr, Brr(1 To 7), Crr, T, i&, j& ', n%, ci%, cj%, ai%
¦C142~¦C145§ï¬°°õ¦æ
¦C147~¦C176§ï¬°µù¸Ñ
¦C191~¦C197§ï¬°°õ¦æ
§Y=7T_·j´M(²Î)¦rÀÉ(¥DÀÉ)_0930(C2_C26=fx)¡A
«h¦C191~¦C197ªºµ{¦¡½X´N¥i¥H°õ¦æ¡C

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

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-4 09:46 ½s¿è

¦^´_ 15# samwang
´ú¸Õµ²ªG :
°ÝÃD1 :
¦C147 §ï¬°    Crr = .Range("c2:ay16"): Arr = .Range("c47:ay" & R): n = 0
°ÝÃD¤w¸Ñ¨M

°ÝÃD2 :
¦]¬°²Ó¬d«á~µo²{BUG¥X¦b  .Font.Name = "Verdana" '¦rÅé¡F
¦Ó§Ú©Ò»Ý­nªº¬O .EntireColumn.AutoFit  '¦Û°ÊÄæ¼e (Sheets("Sheet1")¹w¥ý³]©wªº"¦Û°ÊÄæ¼e"~µL®Ä~¦]¬°¨C´ÁªºÄæ¼e¬O¯B°Ê¤£©wªº)

¦p¶Q«Øij~±N .Font.Name = "Verdana" '¦rÅé²¾°£
°ÝÃD¤w¸Ñ¨M

ÁÂÁ±zªº­@¤ß«ü¾É©M¼ö¤ßÀ°¦£~¨ü¯q¨}¦h~·P®¦

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD