- ©«¤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-11-28
|
¥»©«³Ì«á¥Ñ 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 |
|