n7822123

¦^´_ 2# b9208

¥ÎCellÀx¦s®æ¤@­Ó¤@­Ó¶Ç­È·|«ÜºC

¦]¬°Cell¬O­Óª«¥ó¡A¦û¤£¤ÖÀx¦s¦ì¤¸ªÅ¶¡¡A¸Ì­±¥]§t¤FÄæ¼e¡B¦C°ª¡BÃC¦â¡B®æ¦¡¡B¦ì¸m.....¤@³s¦êªº¸ê®Æ

©Ò¥H¥ý§âÀx¦s®æªº"­È"¥áµ¹°O¾ÐÅé(°}¦C)¡AµM«á°µ³B²z

³B²z§¹¦A¤@¦¸¥á¦^µ¹Àx¦s®æ¡A³o¤è¦¡®Ä²v¤ñ¸û°ª (°O¾ÐÅé³B²z¦ì¤¸³t«×¤]·|¤ñÂà½L¦¡ºÏºÐ§Ö)

²Ä2¬qµ{¦¡¤w¸g¬O°ª®Äªº¼gªk¡A§Ú¬O·Q¤£¨ì¦p¦ó¥iÀu¤Æ®Ä²v¤F¡A¥uÀ°§A²¤Æµ{¦¡½X

µM«á§Aµ¹ªº¸ê®Æ­¶¡AAÄæ¨SªF¦è¡A©Ò¥H«e¥b¬q¨Sªk°õ¦æ¨ì~ §Ú¦Û¤v²K¥[¬y¤ô¸¹´ú¸Õ

µ{¦¡¦p¤U

Sub main()
Application.ScreenUpdating = False
Worksheets("¿é¥X").Activate
Ci = Array(, 2, 3, 4, 5, 6, 7, 8, 18, 19, 20, 22, 22, 23, 23)
Set Rg = [¸ê®Æ!A1048576].End(xlUp)
If Rg.Row = 1 Then Exit Sub
Arr = Range([¸ê®Æ!AD6], Rg)
Brr = [A5].Resize(UBound(Arr), 15)
[A2] = [¸ê®Æ!A2]
For R = 1 To UBound(Brr): For C = 1 To 14
    If C <= 10 Then Brr(R, C) = Arr(R, Ci(C))
    If C = 11 Or C = 13 Then Brr(R, C) = Left(Arr(R, Ci(C)), 8)
    If C = 12 Or C = 14 Then Brr(R, C) = Right(Arr(R, Ci(C)), 5)
Next C: Next R
With [A5].Resize(UBound(Brr), 15)  'A~OÄæ¶ñ­È+¹º®Ø½u
    .Value = Brr
    .Borders.LineStyle = xlContinuous
End With
With [A5].Resize(UBound(Brr), 10)   'A~JÄæ°µ±Æ§Ç
    .Sort key1:=.Item(2), key2:=.Item(10), Header:=xlNo
End With
With [H5].Resize(UBound(Brr), 2)   'H¡BIÄæ°µ¨ú¥N
    .Replace "*AA*", "AAA"
    .Replace "*BBB*", "BBB"
    .Replace "*CC*", "CCC"
    .Replace "*DDD*", "DDD"
    .Replace "*EEE*", "DDD"
    .Replace "*FFF*", "FFF"
    .Replace "*GGG*", "GGG"
    .Replace "*HH*", "GGG"
    .Replace "*MM*", "MMM"
    .Replace "*LLL*", "LLL"
    .Replace "*QQQ*", "LLL"
    .Replace "*NNN*", "NNN"
    .Replace "*TTT*", "NNN"
End With
End Sub

´ú¸ÕÀɮצp¤U

q1+.rar (21.58 KB)

¤U¸ü¦¸¼Æ: 15

2020-8-22 13:45

n7822123

¦^´_ 4# b9208


¬O°Ñ¦Ò§Aªº­ìµ{¦¡³¡¤À¡A¤U¹Ï

§Ú»{¬°§A¬O§â²¤Æ¹Lªº¸ê®Æ¥á¤W¨Ó¡A¹ê»ÚÁٻݭn¦h§PÂ_AD¨º¤@Ä檺­È

©Ò¥H§Ú¸ê®Æ¨úªº½d³ò¤j¤@ÂI~~ÁקK§A­n§ï¡A¨Sªk§PÂ_

²{¦b¬Ý¨Ó¬O¤@­Ó ¬üÄRªº»~·| ?

¤U¸ü (16.51 KB)

2020-8-22 16:51

n7822123

¦^´_ 6# b9208

½Ð°Ý¦p¨Ì¹ê»ÚÀÉ®×A~AZÄæ¦ì¡A¨Ì¾Ú¡§AD¡¨Äæ¦ì¦³¸ê®Æ¡A«hcopyÄæ¦ì¦p¤U¡G
Ci = Array(, 2, 3, 4, 5, 6, 7, 8, 28, 29, 30, 32, 32, 33, 33)
½Ð°Ý¦p¦ó­×­q

33Äæ¨ìAG¥h¤F¡A¨Ó·½°}¦CArr­n¨ú¸ê®Æ¨ìAGÄæ

¨Ã¥Î²Ä30Äæ(AD)§PÂ_

¦pªG¥u§ï¨Ó·½¸ê®Æªº¦ì¸m¡A¿é¥XªºÄæ¦ì³£¤@¼Ë

­×§ïµ{¦¡¦p¤U (¶ÈºI¤@¤p¬q»¡©ú)

Sub main()
Application.ScreenUpdating = False
Worksheets("¿é¥X").Activate
Ci = Array(, 2, 3, 4, 5, 6, 7, 8, 28, 29, 30, 32, 32, 33, 33)
Set Rg = [¸ê®Æ!A1048576].End(xlUp)
If Rg.Row = 1 Then Exit Sub
Arr = Range([¸ê®Æ!AG6], Rg)
Brr = [A5].Resize(UBound(Arr), 15)
[A2] = [¸ê®Æ!A2]
For R = 1 To UBound(Brr): For C = 1 To 14
   If Arr(R, 30) <> "" Then
        If C <= 10 Then Brr(R, C) = Arr(R, Ci(C))
        If C = 11 Or C = 13 Then Brr(R, C) = Left(Arr(R, Ci(C)), 8)
        If C = 12 Or C = 14 Then Brr(R, C) = Right(Arr(R, Ci(C)), 5)
    End If
Next C: Next R
...
...
...

n7822123

¦^´_ 8# b9208

¦ý¦³¤@­Ó°ÝÃD¡A¿é¥X¤¤®Ø½u¦C¼Æ»P¸ê®Æ¦C¼Æ¬Û¦P¡A¤]´N¬O¿é¥X¤¤¨S¦³¸ê®Æ¦C¼Æ¤]¹º®Ø½u¡C

¦]¬°¦³±ø¥ó¿z¿ï«á¡A¸ê®Æ¦C¼Æ»P¿é¥X¦C¼Æ·|Åܦ¨¤£¤@¼Ë¤F¡A³o­è­è§ïµ¹§A®É¨S¦Ò¼{¨ì

©Ò¥H§Ú·s¼W "Ro" ÅܼƨӬö¿ý¿z¿ï¹L«áªº¦C¼Æ

¥t¡B½Ð±Ð¦p©ó°õ¦æ«e¡A¥ý²M°£¿é¥X¼ÐÃD¦C¥H¤UÀx¦s®æ¤§¤º®e¡B®Ø½u¤Î¶ñº¡ÃC¦â¡A¤£¥Î.Delete¤èªk¡A¦³¨ä¥L¤èªk¥i¥H¹F¦¨¡H

¥ÎClear ¤èªk§Y¥i²M°£¡A§Ú·|§â­×§ïªº¦a¤è¥Î¬õ¦âªí¥Ü

µ{¦¡¦p¤U

Sub main()
Application.ScreenUpdating = False
Worksheets("¿é¥X").Activate
Ci = Array(, 2, 3, 4, 5, 6, 7, 8, 18, 19, 20, 22, 22, 23, 23)
Set Rg = [¸ê®Æ!A1048576].End(xlUp)
If Rg.Row = 1 Then Exit Sub
[A4].CurrentRegion.Resize(, 15).Offset(1).Clear
Arr = Range([¸ê®Æ!W6], Rg)
Brr = [A5].Resize(UBound(Arr), 15)
[A2] = [¸ê®Æ!A2]
For R = 1 To UBound(Brr)
  If Arr(R, 20) <> "" Then
      Ro = Ro + 1
      For C = 1 To 14
            If C <= 10 Then Brr(Ro, C) = Arr(R, Ci(C))
            If C = 11 Or C = 13 Then Brr(Ro, C) = Left(Arr(R, Ci(C)), 8)
            If C = 12 Or C = 14 Then Brr(Ro, C) = Right(Arr(R, Ci(C)), 5)
      Next C
  End If
Next R
With [A5].Resize(Ro, 15)  'A~OÄæ¶ñ­È+¹º®Ø½u
    .Value = Brr
    .Borders.LineStyle = xlContinuous
End With
With [A5].Resize(Ro, 14)   'A~JÄæ°µ±Æ§Ç
    .Sort key1:=.Item(2), key2:=.Item(10), Header:=xlNo
End With
With [H5].Resize(Ro, 2)   'H¡BIÄæ°µ¨ú¥N
    .Replace "*AA*", "AAA"
    .Replace "*BBB*", "BBB"
    .Replace "*CC*", "CCC"
    .Replace "*DDD*", "DDD"
    .Replace "*EEE*", "DDD"
    .Replace "*FFF*", "FFF"
    .Replace "*GGG*", "GGG"
    .Replace "*HH*", "GGG"
    .Replace "*MM*", "MMM"
    .Replace "*LLL*", "LLL"
    .Replace "*QQQ*", "LLL"
    .Replace "*NNN*", "NNN"
    .Replace "*TTT*", "NNN"
End With
End Sub

Àɮצp¤U

q2+2.rar (20.96 KB)

¤U¸ü¦¸¼Æ: 11

2020-8-22 23:01

n7822123

¦^´_ 11# ­ã´£³¡ªL

°Ú~·PÁ·ǤjªÈ¿ù  

§Ú§â­ìµ{¦¡ ªº³o¦æ .Range("a5¡Go" & [j1048576].End(xlUp).Row).Select

¬Ý¦¨¬OA~JÄæ¤F   ¡AÀ³¸Ó¬OA~O¤~¹ï ¡A¬Ý¤Ó§Ö¬Ý¿ù¤F.....

­ìPOÁÙ¨Sµo²{¨ì..... µ{¦¡­×§ï¦p¤U(¦]¬°³£¬OA~O¡A»P¤W­±¦X¨Ö)

Sub main()
Application.ScreenUpdating = False
Worksheets("¿é¥X").Activate
Ci = Array(, 2, 3, 4, 5, 6, 7, 8, 18, 19, 20, 22, 22, 23, 23)
Set Rg = [¸ê®Æ!A1048576].End(xlUp)
If Rg.Row = 1 Then Exit Sub
[A4].CurrentRegion.Resize(, 15).Offset(1).Clear
Arr = Range([¸ê®Æ!W6], Rg)
Brr = [A5].Resize(UBound(Arr), 15)
[A2] = [¸ê®Æ!A2]
For R = 1 To UBound(Brr)
  If Arr(R, 20) <> "" Then
      Ro = Ro + 1
      For C = 1 To 14
            If C <= 10 Then Brr(Ro, C) = Arr(R, Ci(C))
            If C = 11 Or C = 13 Then Brr(Ro, C) = Left(Arr(R, Ci(C)), 8)
            If C = 12 Or C = 14 Then Brr(Ro, C) = Right(Arr(R, Ci(C)), 5)
      Next C
  End If
Next R
With [A5].Resize(Ro, 15)  'A~OÄæ¶ñ­È+¹º®Ø½u+±Æ§Ç
    .Value = Brr
    .Borders.LineStyle = xlContinuous
    .Sort key1:=.Item(2), key2:=.Item(10), Header:=xlNo
End With
With [H5].Resize(Ro, 2)   'H¡BIÄæ°µ¨ú¥N
    .Replace "*AA*", "AAA"
    .Replace "*BBB*", "BBB"
    .Replace "*CC*", "CCC"
    .Replace "*DDD*", "DDD"
    .Replace "*EEE*", "DDD"
    .Replace "*FFF*", "FFF"
    .Replace "*GGG*", "GGG"
    .Replace "*HH*", "GGG"
    .Replace "*MM*", "MMM"
    .Replace "*LLL*", "LLL"
    .Replace "*QQQ*", "LLL"
    .Replace "*NNN*", "NNN"
    .Replace "*TTT*", "NNN"
End With
End Sub