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

[µo°Ý] VBA ¨ç¼Æ¤½¦¡µLªk°õ¦æ

[µo°Ý] VBA ¨ç¼Æ¤½¦¡µLªk°õ¦æ

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-19 00:35 ½s¿è





´ú¸ÕÀÉ.rar (141.04 KB)

»¡©ú¡J
±N¨ç¼Æª©½d¨ÒªºB65¨ç¼Æ¤½¦¡¡A¶K¨ìµ{¦¡¥DÀɪº¦C147¡A
¤£ª¾¦ó¬G?¦C147µ{¦¡½XµLªk°õ¦æ¡A·|§e²{¦p¹Ï¤ùªº°»¿ù(¨ä¾l¤½¦¡ªºµ{¦¡°õ¦æ³£¨S¦³°ÝÃD)¡C
¦C147
[B65].FormulaArray = "=IF(MAX((COUNTIF($B1:B1,""<>"")=COUNTIF(OFFSET($B1,,,,COLUMN($A:$AW)),""<>""))*(SMALL(IF($A3:$A60=""¤p­p"",ROW(3:60)),COUNTIF($B1:B1,""<>""))=ROW(3:60))*$B3:$AX60)=N(OFFSET($A1,SMALL(IF($A3:$A60=""¤p­p"",ROW(3:60)),COUNTIF($B1:B1,""<>""))-1,COLUMN(A1))),OFFSET($A1,SMALL(IF($A3:$A60=""¤p­p"",ROW(3:60)),COUNTIF($B1:B1,""<>""))-1,COLUMN(A1)),"""")"

PS¡J¦C147µ{¦¡½X¥Ø«eÂIºñ¨S¦³°õ¦æ¡C

½Ð°Ý¡J
¦p¦ó¤~¯à¸Ñ¨M°õ ¦æ¤½¦¡µ{¦¡ªºBug°ÝÃD¡H
ÁÂÁ¡I

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-19 13:06 ½s¿è

¦^´_ 1# ziv976688
ª¦¤å§ä¨ì³y¦¨"µLªk³]©wºØÃþ¢à¢é¢ög¢íªºFormulaArrayÄÝ©Ê"ªº­ì¦]¬O : ¤½¦¡¶W¹L255­Ó¦r¤¸¡C
¦ý¥u¦³§ä¨ì2007¥H«áªºª©¥»ªº¸Ñ¨M¤èªk : ¤Ä¿ï¡§«H¥ô¹ïVBA¤uµ{¹ï¶H¼Ò«¬ªº³X°Ý¡¨

¤£ª¾¦³­þ¦ì¤j¤j¯à´£¨Ñ2003ª©¥»ªº¾Þ§@©M¸Ñ¨M¤èªk?
ÁÂÁÂ!

TOP

¦^´_ 2# ziv976688


¤Ó½ÆÂø¤F, ¨S¿ìªk

TOP

¦^´_ 2# ziv976688


¬J¥Îvba, ¦ó¶·½ÆÂø, ±N¦U°Ï¬q¦ì§}¨ú¥X§Y¥i
¹ï¨¤¤­¬q°Ï°ìªº³Ì¤j¼Æ-01.rar (12.64 KB)

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-19 16:57 ½s¿è

¦^´_ 4# ­ã´£³¡ªL
­ã´£ª©¥D :
°£«D¤£±o¤w¡A¤£¤Ó´±½Ð±ÐVBA¡A¦]¬°©È¤U¸ü·½ªºªí®æ¤£¬Û¦P®É¡A¦Û¤v¤£¤@©w·|­×§ï
³o¤@ÃDªº¶Q¸Ñ¡A¦ì§}«Ü®e©öÀ´¡A¦Ó¥B³ºµMÁÙ¥i¥H¾A¥Î©ó¦UºØÄæ¦ì¼Æªº¤£¦P°Ï¬q¤À¥¬~
¯u¬O¯«¸Ñ

ÁÂÁª©¥Dªº¼ö¤ßÀ°¦£~·P®¦¦A·P®¦

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-19 19:21 ½s¿è

¦^´_ 4# ­ã´£³¡ªL


TEST_2.rar (174.7 KB)
­ã´£ª©¥D : ±z¦n !
¤£¦n·N«ä¡A±N¶Q¸Ñµ{¦¡½X¸m¤J¹ê°ÈªºOri¥DÀɤ¤¡A°õ¦æ®É²£¥Í¤@¨Ç¤p°ÝÃD(¦h¤@ÂI¤p¤âÄò¡A¦ý³Ì²×ÁÙ¬O¯à°õ¦æ§¹²¦) ~
¨C°õ¦æ§¹¦¨5­Ó´ú¸ÕÀÉ«á¡A´N·|²£¥Í¤@¦¸°»¿ù´£¥Ü®Ø(½Ð¸Ô¨£µ{¦¡½Xªº¦C155) :
EX : ¥Ø«e´£¨Ñªº13­Ó´ú¸ÕÀÉ¡A°õ¦æ«á·|¦b²Ä6­Ó(11-20)©M²Ä11­Ó(12-08)¦U²£¥Í¤@¦¸°»¿ù(¦pªþ¥ó¹Ï¤ù)¡C
¤]´N¬O»¡ : ¨C¦¸¥u°õ¦æ5­Ó´ú¸ÕÀÉ¡A´N¤£·|²£¥Í°»¿ù¡A°õ¦æ¤@¦¸§Y¥i§¹¦¨¡C
·q½Ð±z¦A½ç¥¿¡CÁÂÁ±z

PS¡J¥Ø«e13­Ó´ú¸ÕÀɪº¾Þ§@²Óªp»¡©ú
ÂI¡÷«ö¶s1
·í¸õ¥X°»¿ù´£¥Ü®Ø¡÷¿ïµ²§ô
¦A¸õ¥X¤w¶}±ÒÀɮס÷Ãö³¬
¦A¸õ¥X¬O§_Àx¦sÀɮס÷¿ï§_
¦AÂI¡÷«ö¶s1
¦A¸õ¥X¬O§_¨ú¥NÀɮס÷¿ï¬O
ª½¨ì°õ¦æ§¹²¦¡C

TOP

¦^´_ 6# ziv976688


N = 0: Cx = 0  '´«ÀÉ«á, ÅܼƭnÂk¹s
For Each xR In [A2:A60]
    If xR Like "*_*" Then N = N + 1
    If xR = "¤p­p" Then
       S1 = xR(1, Cx + 2).Resize(1, N).Address
       S2 = xR(1, Cx + 2).Address(0, 0)
       Cells(65, Cx + 2).Resize(1, N) = "=IF(MAX(" & S1 & ")=" & S2 & "," & S2 & ","""")"
       Cx = Cx + N: N = 0
    End If
Next

TOP

¦^´_ 7# ­ã´£³¡ªL
§¹¦¨¤F
ÁÂÁª©¥Dªº¼ö¤ßÀ°¦£©M­@¤ß«ü¾É~·P®¦

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-24 08:18 ½s¿è

¦^´_ 7# ­ã´£³¡ªL
¯B°Ê¨´¤î¦Cªº»yªk¡C
µ{¦¡½X¶i¶¥.rar (12.68 KB)
­ã´£ª©¥D¡J
·Q±NFor Each xR In [A2:A60]ªº½d³ò¨´¤î¦C(A60)¡A
§ï¬°³Ì«á1­Ó"¤p­p"¦C©Î"Á`­p"¦ì§}¦C ¡V 2 ¦C
¥H§Qµ{¦¡½X¯à¾A¥Î¦UºØ¤£¦Pªº¬q¸¨±Æ§Ç¡C

½Ð°Ý¡J³o¼Ë¥i¦æ¶Ü¡H
¦pªG¥i¦æ~
¯à§_¦A½Ð±zÀ°¦£½s¼g¯B°Ê¨´¤î¦Cªº»yªk¡H
ÁÂÁ±z

TOP

¦^´_ 9# ziv976688


N = 0: Cx = 0  '´«ÀÉ«á, ÅܼƭnÂk¹s
dim xE as range
set xE=[A:A].find("Á`­p",lookat:=xlwhole)
For Each xR In range("A2:A" & xE.row-2)
    If xR Like "*_*" Then N = N + 1
    If xR = "¤p­p" Then
       S1 = xR(1, Cx + 2).Resize(1, N).Address
       S2 = xR(1, Cx + 2).Address(0, 0)
       Cells(65, Cx + 2).Resize(1, N) = "=IF(MAX(" & S1 & ")=" & S2 & "," & S2 & ","""")"
       Cx = Cx + N: N = 0
    End If
Next

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD