如何將序號(籃號)B2改為截取C2最後2數為序號(籃號)
將籃號(序號)(B欄)更改以編號(C欄)後2位數
如後面有S則以S前2位數
Sub 派車表_輸入()
Dim DD, CC$, Arr, Brr, i&, j%, N&, xD
Call 派車表_清除
DD = [D1]: CC = [B1]
If Not IsDate(DD) Then MsgBox "**請輸入日期!! ": Exit Sub
If CC = "" Then MsgBox "**請輸入[車編]!! ": Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([工作表2!L1], [工作表2!A65536].End(xlUp))
ReDim Brr(1 To UBound(Arr), 1 To 4)
For i = 2 To UBound(Arr)
If Arr(i, 12) <> DD Or Arr(i, 10) <> CC Then GoTo 101
PNo = Arr(i, 11): If xD(PNo) = 1 Then GoTo 101
xD(PNo) = 1: N = N + 1: Arr(N, 2) = Format(N, "00") '有資料時Arr的第2欄位，自動產生序號
For j = 1 To 4
Brr(N, 1) = Arr(i, 1)
Brr(N, 3) = Arr(i, 2)
Brr(N, 2) = Arr(N, 2)
Next j
Brr(N, 4) = Arr(i, 11)
101: Next i
If N = 0 Then MsgBox "**沒有符合的資料!! ": Exit Sub
With Sheets("工作表1")
.[A3].Resize(N, UBound(Brr, 2)) = Brr
.Range("A1:G" & N + 3).Name = "'" & .Name & "'!Print_Area"
.Range("1:3").Name = "'" & .Name & "'!Print_Titles"
End With
End Sub
 派車表序號修訂.zip (43.86 KB)
|
發表於 2021-6-4 11:27
| 
