jcchiang

¦^´_ 5# qaqa3296

§âÀs¤jªºµ{¦¡­×§ï¤@¤U
¨Ì»Ý¨Dª½±µ±NLeft©ñ¤Jµ{¦¡¤¤
³W®æªÅ¥Õ¥u¦n¥Î«~¸¹¬d¸ß
°õ¦æµ²ªG»P©Ò»Ý¬Û²Å

    Sub ¼Ò½k¬d¸ß()
Dim Rg As Range, Addr0$, R1&
[K:N].ClearContents
[K1:N1] = Array("«~¸¹", "«~¦W", "³W®æ", "¼Æ¶q")
R1 = 1
With [®w¦s!A:C]
   For Each a In Sheets("¥Ø¼Ð").Range([a2], [a2].End(4))
      If a.Offset(, 2) <> "" Then
         Set Rg = .Find(Left(a.Offset(, 2), 8) & "*", , , xlWhole)
      Else
         Set Rg = .Find(a, , , xlWhole)
      End If
      If Not Rg Is Nothing Then Addr0 = Rg.Address
      Do While Not Rg Is Nothing
         R1 = R1 + 1
         If Rg.Column = 3 Then
           Rg.Resize(, 4).Offset(, -2).Copy Cells(R1, "K")
         Else
           Rg.Resize(, 4).Copy Cells(R1, "K")
         End If
         Set Rg = .FindNext(Rg)
         If Rg.Address = Addr0 Then Exit Do
      Loop
   Next
End With
End Sub

qaqa3296

¬Ý¨ì¦U¦ì¤j¤jªº¦^´_¡A¤~µo²{§Ú»¡±o¤£°÷²M·¡¡A¯u¬O©êºp

¤U¸ü (28.25 KB)

2020-8-19 19:44

1.¦p¦ó¹ï³W®æ¥u¨ú­nªºÃöÁä¦r¡A·Q·Q«Ø­Ó»²§U¬d¸ßÄæ"LEFT¨ç¼Æ"¡AÁÙ¬O¦³¿ìªk­ç°£²Ä¤Gªº"-"«á­±ªº¸ê®Æ?(·Q¤£¥X¦³§ó¦nªº¿ìªk¡A¤º®e«ÜÂø¶Ã)
2.»²§UÄ榳¨Ç¬OªÅ¥Õ¦p¦ó¦C¥X»Ý­nªº¸ê®Æ?
3.§å¦¸±N»²§UÄæ¥þ³¡³£¬d¸ß®w¦s¡AµM«á¦C¨ì"¦¨ªG"¡C
4.¹ê»Úª¬ªp¬OµLªk¥Î«~¦W¥h¿z¿ï¸ê®Æªº¡A¤j¬ù500µ§³£¬O¬Û¦P«~¦W¡A¦ý«Ü¦h³£¬O§Ú¤£»Ý­nªº¸ê®Æ

¤U¸ü (71.33 KB)

2020-8-19 19:44

§Æ±æ³Ì«á±o¨ìµ²ªG¡C

·PÁ¦U¦ì¤j¤jÀ°¦£

­ã´£³¡ªL

´M§ä¥Ø¼Ð¥u¦³¤@­Ó, ¥Î¿z¿ï¦A¶K§Y¥i

hcm19522

{=INDEX(®w¦s!A:A,SMALL(IF(ISNUMBER(FIND($J$2,®w¦s!$C$2:$C$45)),ROW($2:$45),99),ROW(A1)))&""

n7822123

¦^´_ 1# qaqa3296

¹ï©óVBA¨Ó»¡¡A³o¬O piece of cake

«ö¶À¦â¬d¸ß«ö¶s§Y¥i!

µ{¦¡¦p¤U

Sub ¼Ò½k¬d¸ß()
Dim Rg As Range, Addr0$, R1&
[K:N].ClearContents
With [®w¦s!C:C]
    Set Rg = .Find([J2] & "*", , , xlWhole)
    If Not Rg Is Nothing Then Addr0 = Rg.Address
    Do While Not Rg Is Nothing
        R1 = R1 + 1
        Rg.Resize(, 4).Offset(, -2).Copy Cells(R1, "K")
        Set Rg = .FindNext(Rg)
        If Rg.Address = Addr0 Then Exit Do
    Loop
End With
End Sub

Àɮצp¤U

¦C¥X§ó¦h¸ê®Æ.rar (17.99 KB)

¤U¸ü¦¸¼Æ: 20

2020-8-19 03:44