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

©m¦W«ö°_©Î¤î¤Î¤é´Á±Æ¦CÅã¥Ü

©m¦W«ö°_©Î¤î¤Î¤é´Á±Æ¦CÅã¥Ü

½Ð°Ý¥ý¶i¡A¤p§Ì¦³­ÓÀÉ®×(¦pªþÀÉ)¡A»Ý­n±N¥ªªíªº¸ê®Æ¡AÅã¥Ü¦¨¥kªíªº±Æ¦C¡A½Ð°Ý¨ç¼Æ¤½¦¡­n¦p¦ó³]©w¡AÁÂÁÂ¥ý¶i¡I ©m¦W«ö°_¤î¤é´Á±Æ¦C.zip (9.57 KB)

¦^´_ 1# shan0948


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹¥DÃD½m²ßVBA°}¦Cªº«Øij¤è®×¤U,½Ð«e½ú¸Õ¸Õ¬Ý

°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$
Brr = Range([G4], Cells(Rows.Count, 1).End(3))
For i = 1 To UBound(Brr)
   T1 = Val(Brr(i, 1)): T6 = Brr(i, 6): T7 = Trim(Brr(i, 7))
   Brr(i, 2) = "": Brr(i, 3) = ""
   If T7 = "" Then GoTo 111
   If T1 > 0 Then
      N = i
      If T7 = "°_" Then Brr(N, 2) = T6
      If T7 = "¤î" Then Brr(N, 3) = T6
      ElseIf T7 = "°_" Then
         Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "¡B" & T6)
      ElseIf T7 = "¤î" Then
         Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "¡B" & T6)
   End If
111
Next
[J4].Resize(N, 3) = Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483


    ·PÁ«e½úªº«ü¾É¡A¦]¤p§Ì»Ý­n¦b¤£¦Pªí¤º¨Ï¥Î¡A¥i§_¨Ï¥Î¨ç¼Æ¤½¦¡¸û¬°Â²³æ¡A·P®¦ÁÂÁ¡I

TOP

¦^´_ 3# shan0948


    ÁÂÁ«e½ú¦^´_
«á¾Ç¨ç¼Æ¤½¦¡«Ü®z,¨ç¼Æ¤½¦¡¤ñVBAÃø,«á¾Ç§V¤O¾Ç²ß¤¤,µ¥¨ç¼Æ¤½¦¡¼F®`ªº«e½ú«ü¾É
¤@°_¾Ç²ß
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-21 11:30 ½s¿è

¦^´_ 3# shan0948


    ÁÂÁ«e½úµoªí¦¹¥DÃD,Åý«á¾Ç¦³½m²ß¦¹±¡¹Òªº¾÷·|
¥H¤U¬O¸ê®Æªí»Pµ²ªGªí¤£¦P¤u§@ªíªº¤è®×,½Ð«e½ú°Ñ¦Ò



Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$, Sh1 As Worksheet, Sh2 As Worksheet
'¡ô«Å§iÅܼÆ:Brr¬O³q¥Î«¬ÅܼÆ,(i,N,T1)¬Oªø¾ã¼ÆÅܼÆ,(T6,T7)¬O¦r¦êÅܼÆ,
'(Sh1,Sh2)¬O¤u§@ªíÅܼÆ

Set Sh1 = Sheets("¸ê®Æªí")
'¡ô¥OSh1³o¤u§@ªíÅܼƬO ¦W¬°"¸ê®Æªí"ªº¤u§@ªí
Set Sh2 = Sheets("µ²ªGªí")
'¡ô¥OSh2³o¤u§@ªíÅܼƬO ¦W¬°"µ²ªGªí"ªº¤u§@ªí
Brr = Range(Sh1.[G4], Sh1.Cells(Rows.Count, 1).End(3))
'¡ô¥OBrr¬O¤Gºû°}¦C!¥H¸ê®Æªí[G4]¨ìAÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ,³o½d³ò­È±a¤J°}¦C¸Ì
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
   T1 = Val(Brr(i, 1)): T6 = Brr(i, 6): T7 = Trim(Brr(i, 7))
   '¡ô¥OT1³oªø¾ã¼ÆÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦C­È,¥HVal()Âà¤Æ¦^¶Ç¼Æ­È,
   '¡ô¥OT6³o¦r¦êÅܼƬO i°j°é¦C²Ä6ÄæBrr°}¦C­È
   '¡ô¥OT7³o¦r¦êÅܼƬO i°j°é¦C²Ä7ÄæBrr°}¦C­È,¥HTrim()¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«áªº·s¦r¦ê

   Brr(i, 2) = "": Brr(i, 3) = ""
   '¡ô¥Oi°j°é¦C²Ä2ÄæBrr°}¦C­È¬O ªÅ¦r¤¸:¥Oi°j°é¦C²Ä3ÄæBrr°}¦C­È¬O ªÅ¦r¤¸
   If T7 = "" Then GoTo 111
   '¡ô¦pªGT7ÅܼƬO ªÅ¦r¤¸!´N¸õ¨ì 111¦ì¸mÄ~Äò°õ¦æ
   If T1 > 0 Then
   '¡ô¦pªGT1ÅܼƤj©ó 0??
      N = i
      '¡ô¥ON³oªø¾ã¼ÆÅܼƬOi°j°é¼Æ
      If T7 = "°_" Then Brr(N, 2) = T6
      '¡ô¦pªGT7ÅܼƬO "°_"!´N¥Oi°j°é¦C²Ä2ÄæBrr°}¦C­È¬O T6ÅܼÆ
      If T7 = "¤î" Then Brr(N, 3) = T6
      '¡ô¦pªGT7ÅܼƬO "¤î"!´N¥Oi°j°é¦C²Ä3ÄæBrr°}¦C­È¬O T6ÅܼÆ
      ElseIf T7 = "°_" Then
      '¡ô§_«h¦pªGT1¬O 0©Î¤p©ó0!¦Ó¥BT7ÅܼƬO "°_"¦r¦ê
         Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "¡B" & T6)
         '¡ô¥Oi°j°é¦C²Ä2ÄæBrr°}¦C­È¬O IIf()¦^¶Ç­È:
         '¦pªGi°j°é¦C²Ä2ÄæBrr°}¦C­È¬OªÅ¦r¤¸,IIf()¦^¶Ç­È¬OT6ÅܼÆ,
         '§_«h,IIf()¦^¶Ç­È¬Oi°j°é¦C²Ä2ÄæBrr°}¦C­È³s±µ"¡B",¦A³s±µT6Åܼƫ᪺·s¦r¦ê

      ElseIf T7 = "¤î" Then
      '¡ô§_«h¦pªGT1¬O 0©Î¤p©ó0!¦Ó¥BT7ÅܼƬO "¤î"¦r¦ê
         Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "¡B" & T6)
         '¡ô¥Oi°j°é¦C²Ä3ÄæBrr°}¦C­È¬O IIf()¦^¶Ç­È:
         '¦pªGi°j°é¦C²Ä3ÄæBrr°}¦C­È¬OªÅ¦r¤¸,IIf()¦^¶Ç­È¬OT6ÅܼÆ,
         '§_«h,IIf()¦^¶Ç­È¬Oi°j°é¦C²Ä3ÄæBrr°}¦C­È³s±µ"¡B",¦A³s±µT6Åܼƫ᪺·s¦r¦ê

   End If
111
Next
Sh2.[J4].Resize(N, 3) = Brr
'¡ô¥Oµ²ªGªí[J4]ÂX®i¦V¤UN¦C,¦V¥kÂX®i3Äæ½d³òªºÀx¦s®æ¥HBrr°}¦C­È±a¤J
Application.Goto Sh2.[J4]
'¡ô¥OÀx¦s®æ´å¼Ð¸õ¨ì µ²ªGªí[J4]¦ì¸m
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

TOP

¦^´_ 5# Andy2483


    ·PÁÂ¥ý¶iªº«ü¾É¡I

TOP

¦^´_ 6# hcm19522


   «D±`·PÁÂ¥ý¶iªº«ü¾É¡A¸g´ú¸Õ«á²Å¦X»Ý¨D¡A§U¯q¬Æ¤j¡A·P®¦ÁÂÁ¡I

TOP

¦^´_ 6# hcm19522


    ¥ý¶i¦n¡A¤§«eªº¸ê®ÆÅã¥Ü¨ç¼Æ¤½¦¡¡A¦]¹ê»Ú»Ý­n¼W¥[±ø¥ó§PÂ_¡A­ì¨ç¼Æ¤½¦¡½Ð°Ý­n¦p¦ó­×¥¿¡A¸Ô²Ó»¡©ú¦pªþÀÉ¡AÁÂÁ¡I ©m¦W«ö°_¤î¤é´Á±Æ¦C-§ó·s.zip (14.19 KB)

TOP

¦^´_ 9# shan0948


    ÁÂÁ«e½ú
«á¾Ç½m²ßVBA¤è¦¡ªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$, T8$
Brr = Range([H4], Cells(Rows.Count, 1).End(3))
For i = 1 To UBound(Brr)
   T1 = Val(Brr(i, 1)): T6 = Trim(Brr(i, 6)): T7 = Trim(Brr(i, 7))
   T8 = Trim(Brr(i, 8))
   Brr(i, 2) = "": Brr(i, 3) = "": Brr(i, 4) = "": Brr(i, 5) = ""
   If T8 = "" Then GoTo 111
   If T1 > 0 Then
      N = i
      If T8 = "°_" Then Brr(N, 2) = T6
      If T8 = "¤î" Then Brr(N, 3) = T6
      ElseIf T8 = "°_" Then
         Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "¡B" & T6)
      ElseIf T8 = "¤î" Then
         Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "¡B" & T6)
         If T7 = "Â÷¾" Then
            Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "¡B" & T6)
         End If
   End If
   If T8 = "°_" And (T7 = "·s¶i" Or T7 = "¥L°VÂà¤J") Then
      Brr(N, 4) = IIf(Brr(N, 4) = "", T6, Brr(N, 4) & "¡B" & T6)
   End If
   If T8 = "¤î" And T7 = "Â÷¾" Then
      Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "¡B" & T6)
   End If
111
Next
[K4].Resize(N, 5) = Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD