- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
5#
發表於 2023-6-17 13:19
| 只看該作者
謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教
執行結果:
Option Explicit
Sub TEST()
Dim Brr, Crr, V, Z, i&, R&, C%, Y&, X%, T$, T1$, T2$
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([Sheet1!B1], [Sheet1!A65536].End(3))
ReDim Crr(1 To UBound(Brr), 1 To 200)
For i = 2 To UBound(Brr)
If i = 2 Then: Crr(1, 1) = Brr(1, 1) & " \ " & Brr(1, 2): Y = 1
T1 = Brr(i, 1): T2 = Brr(i, 2): T = T1 & "|" & T2
If Z(T) = 1 Then GoTo i01
R = Z(T1): C = Z(T1 & "/c"): C = C + 1: Z(T1 & "/c") = C
If R = 0 Then Y = Y + 1: Crr(Y, 1) = T1: Z(T1) = Y: R = Y
Crr(R, C + 1) = T2: Z(T) = 1
If X < C Then X = C: Crr(1, C + 1) = C
i01: Next
Sheets("Sheet2").UsedRange.Clear
Sheets("Sheet2").[A1].Resize(Y, X + 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub |
|
