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

[µo°Ý] ¨Ì±ø¥ó½Æ»s¤£¦PÄæ¦ì¸ê®Æ»P´M§ä¨ú¥N

Sub TEST()
Dim Arr, TS, Cr, C%, i&, j%, N&
Sheets("¿é¥X").UsedRange.Offset(4, 0).EntireRow.Delete
Arr = Range([¸ê®Æ!A1], Sheets("¸ê®Æ").UsedRange)
Cr = Array(, 2, 3, 4, 5, 6, 7, 8, 18, 19, 20, 22, 22, 23, 23)
For i = 6 To UBound(Arr)
    If Arr(i, 20) <> "" Then N = N + 1 Else GoTo i01
    For j = 1 To UBound(Cr)
        Arr(N, j) = Arr(i, Cr(j))
        If j = 11 Or j = 13 Then Arr(N, j) = Left(Arr(N, j), 8)
        If j = 12 Or j = 14 Then Arr(N, j) = Right(Arr(N, j), 5)
    Next j
i01: Next i
If N = 0 Then Exit Sub
Application.ScreenUpdating = False
With [A5].Resize(N, UBound(Cr))
     .Value = Arr
     .Borders.LineStyle = 1
     .Sort key1:=.Item(2), key2:=.Item(10), Header:=xlNo
     With Range(.Columns(8), .Columns(9))
          For Each TS In Array("AA_A", "BBB_B", "CC_C", "DDD_D", "EEE_D", "FFF_F", "GGG_G", "HH_G", "MM_M", "LLL_L", "QQQ_L", "NNN_N", "TTT_N")
              Cr = Split(TS, "_")
              .Replace "*" & Cr(0) & "*", String(3, Cr(1))
          Next
     End With
End With
End Sub

¼gªk¤j­P¬Û¦P~~
±Æ§Ç¬°¦ó¥u¦³«e10Äæ, ¨º«á­±¤£´N¶Ã¤F®M???

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-23 14:53 ½s¿è

¦^´_ 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
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 12# n7822123
­ã¤j¡BÀs¤j
«D±`·PÁ¤G¦ì¤j¤j«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD