- Sub test()
- Dim arr, ar1, ar2
- Dim x%, i%, k%
- Dim d1 As Object, d2 As Object
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Range("a3:d" & Cells(Rows.Count, 1).End(3).Row)
- ar1 = [f5:i16]: ar2 = [k5:n16]
- For x = 1 To UBound(ar1)
- d1(ar1(x, 1)) = ar1(x, 2) & "-" & ar1(x, 3) & "-" & ar1(x, 4)
- d2(ar2(x, 1)) = ar2(x, 2) & "-" & ar2(x, 3) & "-" & ar2(x, 4)
- Next x
- For i = 2 To UBound(arr)
- If InStr(arr(i, 3), "PD") Or InStr(arr(i, 3), "PD") Then
- k = Application.Match(arr(i, 2), [{15,24,40}]) - 1
- arr(i, 4) = Split(d1(arr(i, 1)), "-")(k)
- Else
- k = Application.Match(arr(i, 2), [{15,24,40}]) - 1
- arr(i, 4) = Split(d2(arr(i, 1)), "-")(k)
- End If
- Next i
- Range("a3").Resize(i - 1, 4) = arr
- End Sub
复制代码 |