- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
36#
發表於 2024-3-5 15:42
| 只看該作者
回復 35# 198188
謝謝前輩指導,請再試試看
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, Q, i&, T$, TT$, A, xA As Range, N%, C%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[B65536].End(3)(1, 0))
xA.Offset(1, 12).Resize(, 3).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) = Z(TT) & "/" & i
Next
Arr = ActiveSheet.[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!J1], [KP!H65536].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
Q = Split(Z(TT) & "/", "/")
For C = 1 To UBound(Q) - 1
If Arr(Q(C), 1) = "" Then
Arr(Q(C), 1) = Crr(1, 1)
ElseIf InStr("/" & Arr(Q(C), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
Arr(Q(C), 1) = Arr(Q(C), 1) & "/" & Crr(1, 1)
End If
Arr(Q(C), N \ 3 + 2) = Crr(1, 3)
Next
End If
Next
Next
ActiveSheet.[M1].Resize(UBound(Brr), 3) = Arr
End Sub |
|
發表於 2024-3-5 15:15
| 
