- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
2#
發表於 2015-7-6 11:09
| 只看該作者
回復 1# marklos
試試看- Option Explicit
- Option Base 1
- 'Option Base 陳述式 在模組層次中用來宣告陣列索引的預設下限。
- '語法
- 'Option Base {0 | 1}
- 'AutoFilter 方法 [自動篩選] 篩選出一個清單。為 Variant
- Sub Ex()
- Dim Ar, Sh As Worksheet, i As Integer
- Ar = Array("V:", "I:")
- Sheets("Sheet1").UsedRange.Clear
- With Sheets("log")
- For i = 1 To UBound(Ar)
- .Range("a1").AutoFilter Field:=1, Criteria1:=Ar(i)
- .Columns(3).Copy Sheets("Sheet1").Cells(1, i)
- Next
- .Range("a1").AutoFilter
- End With
- Sheets("Sheet1").[a1].Resize(, UBound(Ar)) = Ar
- End Sub
- Sub Ex1() '不用篩選複製
- Dim Ar, Ar1(), Ar2(), i As Integer, x As Integer, e As Range
- Ar = Array("V:", "I:")
- ReDim Ar1(UBound(Ar))
- With Sheets("log").Range("A:A")
- For i = 1 To UBound(Ar)
- .Replace Ar(i), "=100/0", xlWhole 'Replace 方法
- '"V:" , "I:" 替換為錯誤值
- x = 0
- With .SpecialCells(xlCellTypeFormulas, xlErrors).Cells '錯誤值的儲存格
- .Value = Ar(i) '錯誤值還原為的"V:" , "I:"
- ReDim Ar2(.Cells.Count)
- For Each e In .Offset(, 2).Cells
- x = x + 1
- Ar2(x) = e
- Next
- Ar1(i) = Ar2
- End With
- Next
- End With
- With Sheets("Sheet1")
- .UsedRange.Clear
- For i = 1 To UBound(Ar)
- .Cells(i) = Ar(i)
- .Cells(i).Offset(1).Resize(UBound(Ar1(i))) = Application.Transpose(Ar1(i))
- Next
- End With
- End Sub
|
|
發表於 2015-7-5 22:36
| 
