- 帖子
- 1446
- 主題
- 40
- 精華
- 0
- 積分
- 1470
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-10-21
|
4#
發表於 2023-4-14 11:55
| 只看該作者
謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教
執行前:
執行結果:
Option Explicit
Sub TEST()
Dim Brr, Crr, V, Y, R&, i&, j&, N&, T$, Tv
Dim xR As Range, Sh As Worksheet
Set Y = CreateObject("Scripting.Dictionary")
Set Sh = Sheets("5Y-Data")
Set xR = Intersect(Sh.[A1].CurrentRegion, Sh.UsedRange.Offset(2, 0))
Brr = xR
For i = 1 To UBound(Brr)
For j = 1 To UBound(Brr, 2) Step 2
T = Brr(i, j): Tv = Brr(i, j + 1)
If i = 1 Then T = "Date"
If T = "" Then GoTo i01
Y(T) = Y(T) + 1: Y(T & "|c") = j \ 2 + 1
Y(T & "|" & Y(T & "|c")) = Tv
If Y(T) = 6 Then R = R + 1
Next
i01:
Next
ReDim Crr(1 To R, 1 To 7)
For Each V In Y.keys
If Y(V) = 6 And InStr(V, "|") = 0 Then
N = N + 1: Crr(N, 1) = V
For j = 1 To 6
Crr(N, j + 1) = Y(V & "|" & j)
Next
End If
Next
With Sh.[O3].Resize(R, 7)
.EntireColumn.ClearContents
.Value = Crr
End With
Set Y = Nothing: Set xR = Nothing: Set Sh = Nothing: Erase Brr, Crr
End Sub |
|
