Excel VBA -.Find method between workbooks

Question

Question:
Why Range.Finddoes the method not work when referring to another book?

Problem: I am trying to copy data between books, but the method Range.Findstops using "Runtime Error 1004". I am using Excel 2007 on a computer running Windows 7.

Details: In two books, only Sheet1 is referenced or used for each book. I have a procedure (ztest) with the following loop:

• Format sheet
• Iterate over all cells in column E of book No. 1
• Using the method Range.Find, find the value in column E of book # 2
• Once you find, set column number 1 to column offset = column number 2 of book <2>

I would like to do this with .Find- without using HLOOKUP or the like.

I simplified the code a bit to narrow down what exactly is going on. This does not show step 4 above, but an error occurs in step 3 in the statement containing the method .Find:

Public Sub ztest2()
'set workbook titles
Const w1 As String = "05AR 20130920.xlsx"
Const w2 As String = "05AR 20130923.xlsx"
Dim cl As Variant

With Workbooks(w2).Worksheets(1)
  'format the sheet
  .Range("A1", "D1").EntireColumn.Hidden = True
  'loop through all cells column E of workbook #1
  For Each cl In .Range("E2", Cells(Rows.Count, "E").End(xlUp))
    'find value of current cell in column E, workbook #2
    Workbooks(w1).Worksheets(1) _
    .Range("E2", Cells(Rows.Count, "E").End(xlUp)) _
    .Find(what:=cl.Value, LookIn:=xlValues).Select
  Next
End With

End Sub

Answer 1

It is very important that you structure your code well so that there is no understanding of it. If necessary, write additional lines of code so that even if you see the code after 6 months, you can determine what your code does. Also fully qualify your facilities.

Try this (UNTESTED). I commented on the code. So if you don’t understand something, then send back

Const w1 As String = "05AR 20130920.xlsx"
Const w2 As String = "05AR 20130923.xlsx"

Sub ztest2()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell as Range
    Dim lRowW1 As Long, lRowW2 As Long

    '~~> Define your workbook and worksheets here
    Set wb1 = Workbooks(w1)
    Set ws1 = wb1.Sheets(1)
    Set wb2 = Workbooks(w2)
    Set ws2 = wb2.Sheets(1)

    '~~> Work with First workbook to get last row and define your range
    With ws1
        lRowW1 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws1Rng = .Range("E2:E" & lRowW1)
    End With

    '~~> Work with Second workbook to get last row and define your range
    With ws2
        .Range("A1", "D1").EntireColumn.Hidden = True

        lRowW2 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws2Rng = .Range("E2:E" & lRowW2)

        For Each cl In ws2Rng
            '~~> Do the find
            Set acell = ws1Rng.Find(what:=cl.Value, LookIn:=xlValues)

            '~~> Check if found or not. This is required else you will
            '~~> get an error if no match found
            If Not acell Is Nothing Then
                '
                '~~> Do what ever you want here
                '
            End If
        Next
    End With
End Sub