- 帖子
- 6
- 主題
- 2
- 精華
- 0
- 積分
- 22
- 點名
- 0
- 作業系統
- XP
- 軟體版本
- Office 2003
- 閱讀權限
- 10
- 性別
- 男
- 來自
- New Taipei City
- 註冊時間
- 2010-12-10
- 最後登錄
- 2011-1-1
|
請各位高手大哥大姐幫忙
我的資料如下
A B C
1 Tom 1 0
2 Jeff 2 2
3 Henry 0 1
4 Jerry 5 5
5 May 1 1
6 Tina 0 0
.
.
..
1000
我要將B欄或C欄值為1的人明複製到另一個工作表，處理後結果如下
A B C
1 Tom 1 0
2 henry 0 1
3 may 1 1
.
.
小弟資料有1000多筆，請幫忙
小弟分2段寫成VBA
第一段OK
Sub 選擇範圍()
Dim i%, j%
Dim ta As Range, tb As Range, tc As Range
With Worksheets(1)
i = Cells(Rows.Count, "a").End(xlUp).Row
j = Range("A1").End(xlToRight).Column
range=(Cells(1, 1), Cells(i, j)).Select
End With
End Sub
第二段OK
Sub 進階篩選與複製()
Dim ta As Range, tb As Range, tc As Range
Set ta = Worksheets(1).Range("a1", "g1310")
Set tb = Worksheets(2).Range("a1", "b2")
Set tc = Worksheets(3).Range("a1")
ta.AdvancedFilter xlFilterCopy, tb, tc
End Sub
我合併兩段程式成為
Sub 進階篩選與複製()
Dim i%, j%
Dim ta As Range, tb As Range, tc As Range
With Worksheets(1)
i = Cells(Rows.Count, "a").End(xlUp).Row
j = Range("A1").End(xlToRight).Column
End With
Set ta = Worksheets(1).Range(Cells(1, 1), Cells(i, j))
Set tb = Worksheets(2).Range("a1", "b2")
Set tc = Worksheets(3).Range("a1")
ta.AdvancedFilter xlFilterCopy, tb, tc
End Sub
結果是出現錯誤，請前被指正!! |
|
發表於 2010-12-12 15:59
| 
發表於 2010-12-12 18:40
| 
