- 帖子
- 1446
- 主題
- 40
- 精華
- 0
- 積分
- 1470
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-10-21
|
5#
發表於 2024-3-4 15:24
| 只看該作者
本帖最後由 Andy2483 於 2024-3-4 16:03 編輯
回復 1# 198188
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range([1!G1], [1!A65536].End(3))
xA.Offset(1, 12).ClearContents: Brr = xA
For i = 2 To UBound(Brr)
T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
Z(TT) = i
Next
Arr = [1!M1].Resize(UBound(Brr), 3)
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!K1], [KP!I65536].End(3)))
For Each Crr In A
Crr = Crr: N = N + 1
For i = 3 To UBound(Crr)
T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
If Z.Exists(TT) Then
If Arr(Z(TT), 1) = "" Then
Arr(Z(TT), 1) = Crr(1, 1)
ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
End If
Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
End If
Next
Next
[1!M1].Resize(UBound(Brr), 3) = Arr
End Sub |
|
