- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
本帖最後由 c_c_lai 於 2013-5-18 09:18 編輯
回復 7# sunnyso
回復 5# GBKEE
回復 8# Hsieh
謝謝 GBKEE 以及 Hsieh 兩位大大的範例。
茲將它們稍稍引用：- Option Explicit
- Sub EX() ' GBKEE
- Dim A As Variant, B As Variant, C As Variant, I As Integer
- A = Array(Array("A1", "B1", "C1"), Array("A2", "B2", "C2") ,Array("A3", "B3", "C3"), Array("A4", "B4", "C4"))
- B = Array(Array("A5", "B5", "C5"), Array("A6", "B6", "C6"))
- C = A
- For I = 0 To UBound(B)
- ReDim Preserve C(0 To UBound(C) + 1)
- 'ReDim 陳述式 在程序層次中用來重新配置動態陣列變數的儲存空間。
- 'Preserve 當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字
- C(UBound(C)) = B(I)
- Next
- Range("A1").CurrentRegion = ""
- ' UBound(C) = 5, UBound(C(0)) = 2
- [A1].Resize(UBound(C) + 1, UBound(C(0)) + 1) = Application.Transpose(Application.Transpose(C)) ' 陣列A寫入工作表
- End Sub
- Sub EX2() ' Hsieh
- Dim A As Variant, B As Variant, C As Variant, D As Variant, I As Integer
- Range("A1").CurrentRegion = ""
- A = Array(Array("A1", "B1", "C1"), Array("A2", "B2", "C2"), Array("A3", "B3", "C3"), Array("A4", "B4", "C4"))
- B = Array(Array("A5", "B5", "C5"), Array("A6", "B6", "C6"))
- ' UBound(A) = 3, UBound(A(0)) = 2
- [A1].Resize(UBound(A) + 1, UBound(A(0)) + 1) = Application.Transpose(Application.Transpose(A)) ' 陣列A寫入工作表
- ' UBound(A) = 3, UBound(B) = 1, UBound(B(0)) = 2
- [A1].Offset(UBound(A) + 1).Resize(UBound(B) + 1, UBound(B(0)) + 1) = Application.Transpose(Application.Transpose(B)) ' 陣列B寫入工作表
- D = Range("A1").CurrentRegion.Value ' 將儲存格資料存成新陣列
- Range("A1").CurrentRegion = ""
- ' UBound(D, 1) = 6, UBound(D, 2) = 3
- [A1].Resize(UBound(D, 1), UBound(D, 2)) = D ' 寫回工作表
- End Sub
|
|
發表於 2013-5-18 09:15
| 