- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2024-12-18
|
4#
發表於 2021-8-21 09:14
| 只看該作者
回復 3# wayne0303
請測試看看，謝謝
Sub test()
Dim Arr, xD, T$, w1$, w2$, i&, j&, k%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range("d3:w16")
For i = 2 To UBound(Arr) Step 6
k = k + 1
For j = 1 To UBound(Arr, 2)
T = Arr(i, j): If T = "" Then GoTo 99
xD(T & "_" & k) = Arr(i - 1, j)
Next j
99: Next i
Arr = Range("a5:a" & [a5].End(4).Row)
For i = 1 To UBound(Arr)
w1 = Left(Arr(i, 1), 1)
w2 = Mid(Arr(i, 1), 2, 1)
If Asc(w1) > 64 And Asc(w1) < 123 And Asc(w2) > 64 And Asc(w2) < 123 Then
If UCase(w1) = "L" Then
T = Mid(Arr(i, 1), 2, 4) & "/" & Mid(Arr(i, 1), 8, 2) & "_" & 2
Arr(i, 1) = xD(T)
End If
If UCase(w1) = "R" Then
T = Mid(Arr(i, 1), 2, 4) & "/" & Mid(Arr(i, 1), 8, 2) & "_" & 1
Arr(i, 1) = xD(T)
End If
Else
T = Left(Arr(i, 1), 4) & "_" & 3
Arr(i, 1) = xD(T)
End If
Next
Range("b5").Resize(UBound(Arr)) = Arr
End Sub |
|
