Error In Code

[Dazzawm]

I have a code that basically looks for data in column A on sheet 2, in column AE on sheet 1. When that number is found it copies and inserts the entire row above and changes what is in column A to what's in column B on sheet 2 into the copied and inserted row in AE on sheet 1.

All of a sudden I am getting this error on some files. Any ideas please?

Sub FindCopyReplaceAbove()

Application.EnableEvents = False
Application.Calculation = xlCalculationManual = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim rng1    As Range
Dim Dn1     As Range
Dim rng2    As Range
Dim Dn2     As Range
Dim n       As Long
With Sheets("Sheet2")
    Set rng2 = .Range(.Range("A1"), .Range("A" & Rows.count).End(xlUp))
End With
For Each Dn2 In rng2
With Sheets("Sheet1")
    Set rng1 = .Range(.Range("AE2"), .Range("AE" & Rows.count).End(xlUp))
        For n = rng1.count + 1 To 2 Step -1
            With .Range("AE" & n)
            If .Value = Dn2 Then
                .EntireRow.Interior.ColorIndex = 35
                .EntireRow.Copy
                .EntireRow.Insert Shift:=xlUp
                .Offset(-1).Resize(, 2).Value = Dn2.Offset(, 1).Resize(, 2).Value
            End If
            End With
            Next n
   End With
 Next Dn2
 
Application.EnableEvents = True
Application.Calculation = xlCalculationManual = True
Application.ScreenUpdating = True

End Sub

 

[Micron]

My guesses are that at some point you're trying to move cells up when there is no room above or there is no such thing as xlUp for Range.EntireRow.
Step through your code and check your variable values (e.g. row numbers, value of n, etc.) and see if you can spot the issue. I tested on a wb that has the same sheet names but the rows are different of course. It failed when n was 3 and the row count was 2. However, it worked if I changed to xlDown.
This is what leads me to believe up may be the issue although I admit I saw examples of its use, but those used Range and not EntireRow. Sorry I can't be more sure - still learning Excel vba.

 

[Dazzawm]

Thanks for the reply I actually posted the wrong code and it does it when adding below. I can post code if needed, but it is very similar.

 

[Micron]

it does it when adding below.

Then when adding (appending?) below the last row of data you are not shifting at all, or at least there is no need to? If I misunderstood what you meant then perhaps the code and relevant data would help anyone to figure it out. xl2BB is best for posting data if you can use it.

 

[Dazzawm]

Then when adding (appending?) below the last row of data you are not shifting at all, or at least there is no need to? If I misunderstood what you meant then perhaps the code and relevant data would help anyone to figure it out. xl2BB is best for posting data if you can use it.

Thanks but there is too much data and only fails on a couple of files, the vast majority it works fine.

 

[MARK858]

there is no such thing as xlUp for Range.EntireRow

There is. EntireRow is a range, the same as any other range (you can't obviously do a ToLeft or ToRight)

I actually posted the wrong code and it does it when adding below. I can post code if needed, but it is very similar.

Please post your actual code that is failing

 

[Dazzawm]

There is. EntireRow is a range, the same as any other range

Please post your actual code that is failing

Sorry for the confusion, below is the code for the error photo.

Sub FindCopyReplaceBelow()

Application.EnableEvents = False
Application.Calculation = xlCalculationManual = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim rng1    As Range
Dim Dn1     As Range
Dim rng2    As Range
Dim Dn2     As Range
With Sheets("Sheet2")
    Set rng2 = .Range(.Range("A1"), .Range("A" & Rows.count).End(xlUp))
End With
With Sheets("Sheet1")
    Set rng1 = .Range(.Range("AE2"), .Range("AE" & Rows.count).End(xlUp))
End With
    For Each Dn2 In rng2
        For Each Dn1 In rng1
            If Dn1 = Dn2 Then
                Dn1.EntireRow.Interior.ColorIndex = 35
                Dn1.EntireRow.Copy
                Dn1.Offset(1).EntireRow.Insert Shift:=xlDown
                Dn1.Offset(1).Resize(, 2).Value = Dn2.Offset(, 1).Resize(, 2).Value
               
               
            End If
        Next Dn1
    Next Dn2
   
Application.EnableEvents = True
Application.Calculation = xlCalculationManual = True
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

there is too much data

Maybe the problem is that excel is crashing

Maybe the macro could be improved.
Do you have formulas on sheet1?
How many columns do you need to copy, from column A and what is the last column with data?
How long does your macro, when working well, take on your largest file?

If you can answer each of the questions.

 

[MARK858]

What is the address of Dn1 when you try to insert, offset by 1 row and shift down when it errors

 

[Dazzawm]

Maybe the problem is that excel is crashing

View attachment 121722

Maybe the macro could be improved.
Do you have formulas on sheet1?
How many columns do you need to copy, from column A and what is the last column with data?
How long does your macro, when working well, take on your largest file?

If you can answer each of the questions.

No formulas on sheet 1. Columns up to and including A to AY. Macro works well on majority of files and depends on amount of data on sheet 2.