Code fails with runtime error 91 in some case.

[lavinius]

Hi,

Need some help with my current code. when run code on some sheets in a MainWorkbook, it works just fine. but when run in other sheets with longer ranges to match it fails with runtime error 91. highlights the copy paste rows in code. but when i the stop code and close vba editor the macro has still done its job.

In other cases when some of the ranges C F and I are empty, say range F is empty, it fails alltogether.
Also, is there a way to post example files here? Would make it easier to show what goes wrong.

Code 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

All help is appriciated!

Cheers

 

[VoG]

Cross posted Code fails with runtime error 91 in some case.

 

[lavinius]

Ah, sorry forgot to mention that. Thanks for pointing it out.

 

[lavinius]

Problem Solved on cross post [SOLVED] Code fails with runtime error 91 in some case.

Cheers!