- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
3#
發表於 2015-4-1 17:15
| 只看該作者
回復 1# fusayloveme - Option Explicit
- Sub Ex()
- Dim Rng As Range, Ar(), i As Integer
- With Sheets("2013")
- Set Rng = .Range("A1:C1").Resize(.[A1].End(xlDown).Row) 'End(xlDown) '項目可延伸
- Ar = .Range("D1").Resize(Rng.Rows.Count, .[D1].End(xlToRight).Column - 3).Value '監測值
- End With
- With Sheets("處理後資料")
- .UsedRange.Clear
- .[A1:E1] = Array("日期", "測項", "測站", "時間", "測值")
- For i = 2 To Rng.Rows.Count '從2列開始
- With .Cells(.Rows.Count, "A").End(xlUp)
- .Offset(1).Resize(UBound(Ar, 2)) = Rng.Cells(i, "A").Text '日期
- .Offset(1, 1).Resize(UBound(Ar, 2)) = Rng.Cells(i, "B") '測項
- .Offset(1, 2).Resize(UBound(Ar, 2)) = Rng.Cells(i, "C") '測站
- '陣列導入儲存格的值為2維陣列,元素下限索引從1開始.
- .Offset(1, 3).Resize(UBound(Ar, 2)) = Application.Transpose(Application.Index(Ar, 1)) '時間
- .Offset(1, 4).Resize(UBound(Ar, 2)) = Application.Transpose(Application.Index(Ar, 2)) '測值
- End With
- Next
- With .UsedRange.Borders '框線
- .LineStyle = 1
- .Weight = xlThin
- End With
- End With
- End Sub
|
|
發表於 2015-4-1 14:24
| 
