Code fails with runtime error 91 in some case.

Hello.

I have pusseld together a code that matches data between ranges on different workbooks and copy pastes information belonging to sertain ranges from wb2 to wb1.

The code works just fine on sheet "Car" and "Bike" in MainWorkbook. but when run on a more complex sheet "Plane" i get runtime error 91 "objecvariable or with block variable has not been defined". still when i press debug and stop the code it highlights the copy functions for range nr 3. and when i stop code after error and close it, the macro has still done its job as intended.

Code i use is

Option Explicit

Sub Import()
Dim fname As String

Dim Crng As Range, Frng As Range, Irng As Range

Set Crng = ActiveSheet.Range("C7")
Set Crng = Range(Crng, Crng.End(xlDown))

Set Frng = ActiveSheet.Range("F7")
Set Frng = Range(Frng, Frng.End(xlDown))

Set Irng = ActiveSheet.Range("I7")
Set Irng = Range(Irng, Irng.End(xlDown))

fname = ActiveSheet.Name

Workbooks.Open FileName:=Range("M2")
Sheets(fname).Select

Dim Erng As Range, aCell As Range, bCell As Range

Set Erng = ActiveSheet.Range("E5")
Set Erng = Range(Erng, Erng.End(xlDown))

For Each aCell In Crng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next

For Each aCell In Frng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next

For Each aCell In Irng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next
    
ActiveWindow.Close

End Sub

Could anyone please help me find whats wrong and need be fixing?
All the help i can get is appriciated!

Cross Posts at http://www.ozgrid.com/forum/showthread.php?t=181410
& http://www.mrexcel.com/forum/excel-q...some-case.html

Cheers

first correct the check for the find not returning anything.

For Each aCell In Crng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not bCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next

Plane on has 1 data point in column I so the range to process is I7:I1048576, which is a waste of time. Either find the end row by coming up from the bottom when using the End method. or check the 2nd row contains something.

    With ActiveSheet
        set Crng = .Range("C7", .Cells(.Rows.Count, 3).End(xlUp))
    End With

this seems to work fine

Sub Import()
Dim fname As String
Dim Erng As Range, aCell As Range, bCell As Range
Dim Crng As Range, Frng As Range, Irng As Range

With ActiveSheet
    Set Crng = .Range("C7", .Cells(.Rows.Count, 3).End(xlUp))
    Set Frng = .Range("F7", .Cells(.Rows.Count, 6).End(xlUp))
    Set Irng = .Range("I7", .Cells(.Rows.Count, 9).End(xlUp))

    fname = ActiveSheet.Name
End With

Workbooks.Open FileName:=Range("M2")
Sheets(fname).Select

With ActiveSheet
    Set Erng = .Range("E5", .Cells(.Rows.Count, 5).End(xlUp))
End With

For Each aCell In Crng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not bCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next

For Each aCell In Frng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not bCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next

For Each aCell In Irng
    Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not bCell Is Nothing Then
        aCell.Offset(, -1) = bCell.Offset(, -1)
        aCell.Offset(, 1) = bCell.Offset(, -2)
    End If
    Next
    
ActiveWindow.Close

End Sub