- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 3# quickfixer
ÁÂÁ«e½ú
¥H¤U¬ã²ß¤ß±oµù¸Ñ¦p¤U!½Ð«e½ú¦A«ü¾É!
Option Explicit
Sub test_quickfixer()
Dim Find_Num, reg As Object, brr, crr, i&, j&, runtime As Double
'¡ô«Å§iÅܼÆ
runtime = Timer
Application.ScreenUpdating = False
brr = Range([¾Þ§@ªí!B2], [¾Þ§@ªí!B65536].End(3))
'¡ô¥O brr¬O°}¦C!ˤJ ¾Þ§@ªíBÄ檺¸ê®Æ®æÈ
Set reg = CreateObject("VBScript.RegExp")
'¡ô¥O reg¬O¥¿«h
reg.Pattern = "\d+"
'¡ô¥OPattern¬O¼Æ¦r¦ê
reg.Global = True
'¡ô¼È®É¤£À´°µ¤°»ò¥Îªº!¥ý¾ÇµÛ¥Î!
ReDim crr(1 To UBound(brr), 0)
'¡ô«Å§icrr°}¦C½d³ò!Áa¦V¬O1 ¨ìbrr°}¦CÁa¦V¦C¼Æ,¾î¦V¬O 1Äæ
'¤µ¤Ñ¤~ª¾¹D¤GºûªÅ°}¦C³æ¿W 1Äæ¥i¥H 0ªí¥Ü!¥H«e¥uª¾¹D 1 TO 1,
', 0)ªí¥Ü°}¦C 0 TO 0 Äæ,¦]¬°°ß¤@,©Ò¥H¥i¥H¥H 0 ³¯z!UBound(crr, 2)=0
', 1)ªí¥Ü°}¦C 0 TO 1 Äæ!¦@¨âÄæ! ,
', 1 TO 1)¬Oªí¥Ü¤£¥Î²Ä 0Äæ
'¦ý¬O brr = Range([¾Þ§@ªí!B2], [¾Þ§@ªí!B65536].End(3)) ¬O
'±qbrr(1,1)¶}©lÂ\©ñ!¤£¬O±qbrr(0,0)¶}©lÂ\©ñ!¤µ¤Ñ¦n¹³§ó²M·¡¨Ç!ÁÂÁÂ!
For i = 1 To UBound(brr)
'¡ô³]¥~¶¶°j°é±q 1¨ìbrr°}¦CÁa¦V¦C¦ì¼Æ
Set Find_Num = reg.Execute(brr(i, 1))
'¡ô¥O Find_Num¬O°õ¦æ brr°}¦C°j°é¤¸¯À°õ¦æ¥¿«hªºµ²ªG
If Find_Num.Count > 0 Then
'¡ô¦pªG ¥¿«hªºµ²ªG¼Æ¶q¬O>0 !
For j = 0 To Find_Num.Count - 1
'¡ô³]¤º¶¶°j°é±q 0¨ì ¥¿«hªºµ²ªG¼Æ¶q´î 1
'´î1ªºì¦]¬O ¥¿«hªºµ²ªG¬O±q§Ç¸¹ 0¶}©l±Æ§G
crr(i, 0) = crr(i, 0) + Val(Find_Num(j))
'¡ôcrr°}¦C©ñ¤J¦r¦ê¥¿«h¨ú±oªº¼Æ¦r¦r¦êÂà¤Æ¬°¼ÆÈ,
'¦b¤º°j°é¤¤²Ö¥[
Next j
End If
Next i
Range("d2").Resize(UBound(brr) + 10, 2) = crr
'¡ô±q[d2]ÂX®i(brr°}¦C³Ì¤jÁa¦V¦C¦ì¼Æ_¦C,1_Äæ)½d³òÀx¦s®æ,
'¥O¤Wz½d³òÀx¦s®æ µ¥©ócrr°}¦C!
'¤]´N¬On±qcrr°}¦Cªº³Ì¥ª¤W¨¤¸ê®Æ¹ïµ¥[d2]¦ì¸m±a¤Jcrr°}¦CÈ
'°²³]Resize()ªº½d³ò¤ñcrr°}¦C½d³ò¤p! ¥u¨úcrr°}¦Cªº§½³¡½d³ò
'°²³]Resize()ªº½d³ò¤ñcrr°}¦C½d³ò¤j! Resize()¶W¥Xªº§½³¡½d³ò,
'¾î¦V¶W¥X:·|¦A«½Æ°}¦C¥ª¤W¨¤¸ê®Æ¶}©l±a¤J!
'Áa¦V¶W¥X:·|¶K¤J "#N/A" ¦r¦ê
'³o³¡¤Àª¾ÃѤµ¤Ñ¦n¹³§ó²M·¡¨Ç!ÁÂÁÂ!
Application.ScreenUpdating = True
Debug.Print Timer - runtime
'¡ô«á¾Ç³£¥ÎMsgbox! ³o§Y®É¹Bºâµøµ¡Åã¥Ü¤£ª¾¹D¦³¤°»ò¯S§O¦n³B?
'¤µ¤Ñª¾¹D°j°é¸ÌªºMsgboxn«ö«Ü¦h¦¸,¨S®Ä²v!Debug.Print ª½±µÅã¥Ü
'ÁÂÁ«e½ú
Set reg = Nothing
End Sub |
|