- 帖子
- 1429
- 主題
- 40
- 精華
- 0
- 積分
- 1453
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-5-18
|
謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教
資料表:
結果表:
Option Explicit
Sub TEST()
Dim Brr, Y, R&, R1&, i&, T$, Tm$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Set xR = Range([C2], Cells(Rows.Count, "A").End(xlUp)): Brr = xR
For i = 2 To UBound(Brr)
If i = 2 Then
R = R + 1: Brr(1, 1) = "月份": Brr(1, 2) = "最早日期": Brr(1, 3) = "最後日期"
End If
T = Brr(i, 1): Tm = Val(Brr(i, 1)) \ 100
If Y(Tm) = "" Then
R = R + 1: R1 = R: Y(Tm) = R1
Brr(R1, 1) = Tm: Brr(R1, 2) = T: Brr(R1, 3) = T
Else
R1 = Y(Tm)
If T < Brr(R1, 2) Then Brr(R1, 2) = T
If T > Brr(R1, 3) Then Brr(R1, 3) = T
End If
Next
With Workbooks.Add
.Sheets(1).[A1].Resize(R, 3) = Brr
End With
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub |
|
發表於 2023-4-18 15:47
| 