Overflow error - what can I change?

[DanielIcke]

Hi guys,

I hope that you can help me out with this code:

Sub Completed8()

Dim i As Integer
Dim j As Integer

Dim BvDNumberTarget1 As Range
Dim BvDNumberTarget2 As Range
Set BvDNumberTarget1 = Range("BvDNumberTarget1")
Set BvDNumberTarget2 = Range("BvDNumberTarget2")

Application.ScreenUpdating = False

Sheets(4).Activate

For i = 1 To BvDNumberTarget1.Rows.Count
For j = 1 To BvDNumberTarget2.Rows.Count
If BvDNumberTarget1(i) = BvDNumberTarget2(j) Then
Worksheets("Orbis.Target.Online.Data.").Rows(1 + j).Copy
Worksheets("Sample6AccountingData").Rows(1 + i).Insert
Worksheets("Sample6AccountingData").Cells(1 + i, 1) = BvDNumberTarget1(i)
End If
Next
Next

Application.ScreenUpdating = True

End Sub

I have about 16000 rows in range BvDNumberTarget2 - however, I have tried and delete 13000 of those and it still provides me with the same error 6: overflow.

I hope that you can help me out here.

Best Regards,
Daniel Icke

 

[Andrew Poulsom]

Try declaring your variables as Long instead of Integer.

 

[DanielIcke]

I've changed that now, I hope that it will work, but the code takes ages to load, I guess due to the loop?

 

[DanielIcke]

It has now been running for over an hour? Can anyone help with what to improve, hence do differently to minimize the time spent running the code?