將工作表1、工作表2符合條件資料以find整合到工作表3問題

lionliu

各位大哥好：
有個問題請教:
我要將符合工作表3表單內容由工作表1、工作表2帶入問題。
我史以find去寫但是在切換功過表時會出錯。請教我應如何修正。
附上範例

1. Sub sech()
   
2. Dim i, j As Integer
   
3. Dim Nam As String
   
4. 
   
5. For i = 2 To 6
   
6. Nam = Sheets(3).Cells(i, "a")
   
7. Sheets(2).Select
   
8. Selection.Find(What:=Nam, After:=ActiveCell _
   
9.         , LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
   
10.         SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False, SearchFormat _
    
11.         :=False).Activate
    
12. Sheets(3).Cells(i, "b") = ActiveCell.Offset(, 1)
    
13. '無法切換問題
    
14. Sheets(1).Select
    
15. Selection.Find(What:=Nam, After:=ActiveCell _
    
16.         , LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    
17.         SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False, SearchFormat _
    
18.         :=False).Activate
    
19. Sheets(3).Cells(i, "c") = ActiveCell.Offset(, -1)
    
20. Next i
    
21. 
    
22. End Sub

複製代碼

活頁簿3.rar (12.35 KB)

下載次數: 8

2017-4-14 17:09

yen956

試試看
Sub sech()
Dim i As Integer, j As Integer, Rng
Dim Nam As String
For i = 2 To 6
    Nam = Sheets(3).Cells(i, "a")
    Sheets(2).Select
    Set Rng = Range("A:A").Find(What:=Nam, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False, SearchFormat _
        :=False)
    Sheets(3).Cells(i, "b") = Rng.Offset(, 1)

    '無法切換問題
    Sheets(1).Select
    Set Rng = Range("B:B").Find(What:=Nam, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False, SearchFormat _
        :=False)
    Sheets(3).Cells(i, "c") = Rng.Offset(, -1)
Next i
End Sub

lionliu

回復 2# yen956

謝謝啦，我再試一下