- 帖子
- 1446
- 主題
- 40
- 精華
- 0
- 積分
- 1470
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-10-21
|
3#
發表於 2023-4-6 11:14
| 只看該作者
回復 1# coafort
謝謝前輩發表此主題與範例
後學藉此帖練習VBA陣列語字典,學習方案如下,請前輩參考
執行前:
執行結果:
Option Explicit
Sub TEST()
Dim Brr, Crr, Y, R&, C&, i&, T$, V$, Z$, Q$
Dim xR1 As Range, xR2 As Range, Sh1 As Worksheet, Sh2 As Worksheet
Set Y = CreateObject("Scripting.Dictionary")
Set Sh1 = Sheets("工作表1"): Set Sh2 = Sheets("工作表2")
Set xR1 = Sh1.UsedRange.Offset(1, 0): Brr = xR1
Set xR2 = Sh2.UsedRange.Offset(2, 0): Crr = xR2
For i = 1 To UBound(Brr)
If Brr(i, 1) = "" Then GoTo i01 Else T = Brr(i, 1) & "|" & Brr(i, 2)
Y(T & "/配息") = Brr(i, 3): Y(T & "/配股") = Brr(i, 5)
i01:
Next
For C = 1 To UBound(Crr, 2) Step 8
For R = 1 To UBound(Crr)
If Crr(R, C) = "" Then GoTo i02 Else T = Crr(R, C) & "|" & Crr(R, C + 1)
Crr(R, C + 4) = Y(T & "/配息"): Crr(R, C + 6) = Y(T & "/配股")
V = Replace(Cells(R + 2, C + 2).Address, "$", "")
Z = Replace(Cells(R + 2, C + 4).Address, "$", "")
Q = Replace(Cells(R + 2, C + 6).Address, "$", "")
Crr(R, C + 5) = "=IF(" & Z & "," & Z & "*" & V & ","""")"
Crr(R, C + 7) = "=IF(" & Q & "," & Q & "/10*" & V & ","""")"
Next
i02:
Next
xR2 = Crr
Set Y = Nothing: Set xR1 = Nothing: Set xR2 = Nothing: Erase Brr, Crr
Set Sh1 = Nothing: Set Sh2 = Nothing
End Sub
謝謝論壇,謝謝各位前輩 |
|
