- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
回復 2# Hsieh
謝謝前輩,謝謝論壇
後學藉此帖範例練習陣列與字典,學習的方案如下
請前輩們指教
執行前:
執行結果:
Option Explicit
Sub TEST()
Dim Brr, i&, N&, xR, Y, j&, B&, C&
Set Y = CreateObject("Scripting.Dictionary")
Set xR = Range([Sheet1!D1], [Sheet1!A65536].End(3))
Brr = xR
For i = 2 To UBound(Brr)
If Y(Brr(i, 1)) = "" Then
Y(Brr(i, 1)) = Y.Count + 1: N = Y(Brr(i, 1))
For j = 1 To UBound(Brr, 2): Brr(N, j) = Brr(i, j): Next
Else
N = Y(Brr(i, 1))
For j = 2 To 3: Brr(N, j) = Brr(N, j) + Brr(i, j): Next
End If
B = B + Brr(i, 2): C = C + Brr(i, 3)
Next
With xR.Item(1, 14).Resize(Y.Count + 1, UBound(Brr, 2))
.EntireColumn.Clear: .Value = Brr: .Item(Y.Count + 2, 1) = "總計"
.Item(Y.Count + 2, 2) = B: .Item(Y.Count + 2, 3) = C
End With
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub |
|
發表於 2023-3-28 16:39
| 