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

[µo°Ý] ¦p¦ó­×¥¿ Or ªº³¯­z¤£²Å¦X«¬ºA

[µo°Ý] ¦p¦ó­×¥¿ Or ªº³¯­z¤£²Å¦X«¬ºA

¦U¦ì«e½ú¦n
«á¾ÇÀ°¦P¾Ç³B²zµ{¦¡½XµLªk°õ¦æªº°ÝÃD,¨ÃÀ°¦£¤ß±oµù¸Ñ!
½Ð¦U¦ìÀ°¦£«á¾Ç¬Ý¤@¤U­×¥¿¸òµù¸Ñ¬O§_¦³¿ù!ÁÂÁÂ!
­ì°»¿ù°T®§:


­×¥¿«á°õ¦æ«e:


°õ¦æµ²ªG:


'­ìµ{¦¡½X
Option Explicit
Sub TEST_20221102_1()
Dim b&, xU As Range
For b = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    If Cells(1, b) = "NO 4(NI)" Or "HAS 4(I)" Then
       If xU Is Nothing Then Set xU = Cells(1, b) Else Set xU = Union(xU, Cells(1, b))
    End If
Next b
If Not xU Is Nothing Then xU.EntireColumn.Delete
End Sub

'­×¥¿¦p¤U:
Sub TEST_20221102_2()
Dim b&, xU As Range, T
'¡ô«Å§iÅܼÆ:b¬Oªø¾ã¼Æ,xU¬O(ª«¥ó:Àx¦s®æ)
For b = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
'¡ô³]¶¶°j°é!±q1 ¨ì²Ä1¦C³Ì¥kÃ䪺Äæ¼Æ
    If Cells(1, b) = "NO 4(NI)" Or Cells(1, b) = "HAS 4(I)" Then
   '¡ô¦pªG°j°éÄæ/²Ä1¦C = "NO 4(NI)"¦r¦ê ©Î °j°éÄæ/²Ä1¦C = "HAS 4(I)"¦r¦ê
   
    'Or ªº¨Ï¥Î¤£¯à¹³»¡¸Ü²²¤!¨Ò¦p:
    '¦pªG§A¤µ¤Ñ¤¤¤È­n ¥~¥X¶R«K·í ©Î ¶§¬KÄÑ ,½ÐÀ°§Ú¶R¤@¥÷
    '¦bµ{¦¡½X¸Ì:
    '¦pªG§A¤µ¤Ñ¤¤¤È­n ¥~¥X¶R«K·í ©Î ¥~¥X¶R¶§¬KÄÑ ,½ÐÀ°§Ú¶R¤@¥÷

   
       If xU Is Nothing Then
       '¡ô¦pªG xU¨S¦³ª«¥ó
          Set xU = Cells(1, b)
          '¡ôif±ø¥ó¦¨¥ß!´N¥OxU¸Ë¤J(°j°éÄæ/²Ä1¦CÀx¦s®æ)
          Else
             Set xU = Union(xU, Cells(1, b))
             '¡ô¦pªGif±ø¥ó¤£¦¨¥ß(¤]´N¬O»¡xU¤w¸g¸Ë¹LÀx¦s®æ¤F!
             '´N§â(°j°éÄæ/²Ä1¦CÀx¦s®æ)Ä~Äò©ñ¤J xUÀx¦s®æ¶°¸Ì

       End If
    End If
Next b
If Not xU Is Nothing Then xU.EntireColumn.Delete
'¡ô¦pªGxUÀx¦s®æ¶°¸Ì¦³ª«¥óÀx¦s®æ!´N§â³o¨ÇÀx¦s®æ©Ò¦bªºÄæ§R°£
End Sub

¥»©«³Ì«á¥Ñ quickfixer ©ó 2022-11-3 03:24 ½s¿è

¦^´_ 1# Andy2483

¥Îregexp¦n¹³¤ñ¸û¤è«K,³t«×ÁÙ§Ö¤@¨Ç

    Sub test()
    Dim Find_Num, reg As Object, brr, crr, i&, j&, s As Double, runtime As Double
    runtime = Timer
    Application.ScreenUpdating = False
    brr = Range([¾Þ§@ªí!B2], [¾Þ§@ªí!B65536].End(3))
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "\d+"
    reg.Global = True
    ReDim crr(1 To UBound(brr), 0)
    For i = 1 To UBound(brr)
        s = 0
        Set Find_Num = reg.Execute(brr(i, 1))
        If Find_Num.Count > 0 Then
            For j = 0 To Find_Num.Count - 1
                s = s + Find_Num(j)
            Next j
        End If
        crr(i, 0) = s
    Next i
    Range("d2").Resize(UBound(brr), 1) = crr
    Application.ScreenUpdating = True
    Debug.Print Timer - runtime
    Set reg = Nothing
End Sub

TOP

¦^´_ 2# quickfixer


    ¦h¥Î¤F¤@­ÓÅܼÆ,­×¥¿¦p¤U
Sub test()
    Dim Find_Num, reg As Object, brr, crr, i&, j&, runtime As Double
    runtime = Timer
    Application.ScreenUpdating = False
    brr = Range([¾Þ§@ªí!B2], [¾Þ§@ªí!B65536].End(3))
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "\d+"
    reg.Global = True
    ReDim crr(1 To UBound(brr), 0)
    For i = 1 To UBound(brr)
        Set Find_Num = reg.Execute(brr(i, 1))
        If Find_Num.Count > 0 Then
            For j = 0 To Find_Num.Count - 1
                crr(i, 0) = crr(i, 0) + Val(Find_Num(j))
            Next j
        End If
    Next i
    Range("d2").Resize(UBound(brr), 1) = crr
    Application.ScreenUpdating = True
    Debug.Print Timer - runtime
    Set reg = Nothing
End Sub

TOP

¦^´_ 3# quickfixer

ÁÂÁ«e½ú«ü¾É!
ªì¨Bª¾ÃѬd¸ß CreateObject("VBScript.RegExp")¬O¥¿«hªº»yªk,±q¨S¾Ç¹L
¶}©l¥¿«hªº¾Ç²ß! ¸É¤W½d¨ÒÀÉ
¦r¦ê¥¿«h_¼Æ¦rÁ`¦X.zip (341.02 KB)

«e½úªºµ{¦¡°õ¦æ®É¶¡¤ñ¸ûµu:


«á¾Çªºµ{¦¡°õ¦æ®É¶¡¤ñ¸ûªø:

TOP

¦^´_ 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¤W­z½d³òÀx¦s®æ µ¥©ócrr°}¦C!
    '¤]´N¬O­n±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°é¸ÌªºMsgbox­n«ö«Ü¦h¦¸,¨S®Ä²v!Debug.Print ª½±µÅã¥Ü
    'ÁÂÁ«e½ú

    Set reg = Nothing
End Sub

TOP

¦^´_ 3# quickfixer

ÁÂÁ«e½ú
¤µ¤Ñ½m²ß¥Î¥¿«hµo²{:
¦pªG¨S¦³¥[  reg.Global = True
¥¿«hµ²ªG¥u¦³¤@µ§
ÁÂÁÂ

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 7# hcm19522
ÁÂÁ«e½ú«ü¾É
§â¤@¦æ¤½¦¡¶ñ¤JÀx¦s®æ´N¯à±o¨ìµª®×!
¬O«á¾Çªº¾Ç²ß¥Ø¼Ð!³oÅÞ¿è­n«Ü²M·¡!
«e½úªº¨«°g®c¤À¨É¦³¤U¸ü¨Ó¬Ý,¤Ó¼F®`¤F
ÁÂÁ«e½ú

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD