- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-9-30
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-1 15:56 ½s¿è
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Brr, Crr, i&, R&, N&, T$, T1$
'¡ô«Å§iÅܼÆ
Brr = Range([C1], [B65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¸Ë¤JÀx¦s®æȪº¤Gºû°}¦C
ReDim Crr(1 To 1000, 1 To 2)
'¡ô«Å§iCrrÅܼƬO¤GºûªÅ°}¦C,«Å§i¨ä©Ò¤Þ¸¹½d³ò
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2 ¨ì Brr°}¦CÁa¦V³Ì¤j¦C¸¹
R = Val(Brr(i, 1)): T1 = Trim(Brr(i, 2))
'¡ô¥ORÅܼƬO °j°é¦C1Äæ°}¦CÈÂনªº¼ÆÈ(¶g¼Æ)
'¥OT1ÅܼƬO °j°é¦C2Äæ°}¦CÈ¥h°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê(¤º®e)
If R = 0 Or T1 = "" Then GoTo i01 Else Crr(R, 1) = R
'¡ô¦pªG§Ç¸¹©Î¤º®e¬OªÅªº !´N¸õ¹L,§_«h¥Oµ²ªG°}¦CCrr²Ä1Äæ¶g¼Æ¦C¼g¤J¶g¼Æ
If InStr(T & "/", "/" & T1 & "/") Then GoTo i01 Else N = IIf(N < R, R, N)
'¡ô¦pªG¦ê±µ¤º®eªº¦r¦ê¸Ì¦³³o T1ÅܼƤº®e!´N¸õ¹L,
'§_«h¥ONÅܼưO¿ýµ²ªG°}¦C»Ýn¥Î¦h¤Ö¦C
Crr(R, 2) = IIf(Crr(R, 2) = "", T1, Crr(R, 2) & ":" & T1)
'¡ô¥Oµ²ªG°}¦CCrr²Ä2Ä椺®e¦C¼g¤J¨S¦³«½Æªº¤º®e
T = T & "/" & T1
'¡ô¥OTÅܼƲ֦걵T1ÅܼÆ(¤º®e)
i01: Next
[H:I].ClearContents
'¡ô¥Oªºµ²ªGÀx¦s®æ¸ê®Æ²M°£¤º®e
If N = 0 Then Exit Sub
'¡ô¦pªGNÅܼƬO0(¨S¦³²Å¦Xªº¸ê®Æ)!´Nµ²§ôµ{¦¡°õ¦æ
[H3].Resize(N, 2) = Crr
'¡ô¥O©Ò»Ýnªºµ²ªGÀx¦s®æ°Ï°ì¼g¤JCrr°}¦CÈ
End Sub |
|