­ã´£³¡ªL

Sub §ó·s()
Dim Arr, Brr, C&, i&, T1$, T2$, U$, N&
Call ²M°£
Arr = Range([¥X³f¤Î¨ú³f!F1], [¥X³f¤Î¨ú³f!A1].Cells(Rows.Count, 1).End(xlUp))
ReDim Brr(1 To UBound(Arr), 1 To 11)
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1): If T1 = "" Then GoTo 101
    If T1 = "OS" Then
       If Arr(i, 6) <> "PRINT CUP" And Left(Arr(i + 1, 1), 2) <> "YP" Then T2 = "": GoTo 101
       T2 = "P": N = N + 1
       Brr(N, 1) = Arr(i - 1, 3): Brr(N, 2) = Arr(i - 1, 2): Brr(N, 3) = "#" & Arr(i - 1, 1)
    End If
    U = Left(T1, 1) & Right(T1, 1)
    C = Switch(U = "Y9", 4, U = "Y1", 7, U = "Y7", 10, U = U, 0)
    If T2 = "" Or C = 0 Then GoTo 101
    Brr(N, C) = T1:   Brr(N, C + 1) = Arr(i, 3) * IIf(Left(T1, 2) = "YP", -1, 1)
101: Next i
If N = 0 Then Exit Sub
With [¥X³fµ²¾l!B7:L7].Resize(N)
     .Value = Brr
     .Columns(1).NumberFormatLocal = "m/d"
     .Borders.LineStyle = 1
End With
End Sub
'======================================
Sub ²M°£()
Sheets("¥X³fµ²¾l").UsedRange.Offset(6, 0).EntireRow.Delete
End Sub

¦¬¶°¥X³f¤Î¨ú¦^¤§¼Æ¶q.rar (42.93 KB)

¤U¸ü¦¸¼Æ: 10

2018-10-13 11:51