VBA macro Error 6 overflow

Home / vba / VBA macro Error 6 overflow

Question:
So I’ve been doing some reading into the overflow error, and updated all of the integers I was using to longs. I’m still having some issues with the error though. My excel macro is a glorified copy, looping through two tables to combine costs with matching values.
Sub MoveActual()
‘Speed up macro section
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Long ‘Row in FcstAll
Dim j As Long ‘Row in GL – Actuals
Dim FcstAll As Worksheet
Dim GlActuals As Worksheet
Dim FailedCopy As Boolean

Set FcstAll = Sheets("Forecast ALL")
Set GlActuals = Sheets("GL – Actuals All")
j = 2 ‘Initialize the GL – Actuals Loop

Do While GlActuals.Cells(j, 2).Value <> "" ‘Loop through GL

i = 3 ‘initialize the Forecast All Loop
FailedCopy = True ‘initialize Copy Check

Do While FcstAll.Cells(i, 2).Value <> "" ‘Loop through Fcst

‘Find Match between Fcst and GL
If ((GlActuals.Cells(j, 2).Value = FcstAll.Cells(i, 2).Value And _
GlActuals.Cells(j, 3).Value = FcstAll.Cells(i, 3).Value) And _
Not (GlActuals.Cells(j, 17).Value = "Found")) Then

‘Copy over actual
FcstAll.Range(FcstAll.Cells(i, 22), FcstAll.Cells(i, 33)).Value = _
GlActuals.Range(GlActuals.Cells(j, 5), GlActuals.Cells(j, 16)).Value
FailedCopy = False
GlActuals.Cells(j, 17).Value = "Found"

End If
i = i + 1 ‘incriment i to continue loop

Loop

If FailedCopy Then

‘Copy over Details
FcstAll.Range(FcstAll.Cells(i, 1), FcstAll.Cells(i, 3)).Value = _
GlActuals.Range(GlActuals.Cells(j, 1), GlActuals.Cells(j, 3)).Value
FcstAll.Cells(i, 4).Value = "GL – Actuals All"
FcstAll.Range(FcstAll.Cells(i, 5), FcstAll.Cells(i, 22)).Value = 0
‘Copy over missing Finance Data
FcstAll.Range(FcstAll.Cells(i, 22), FcstAll.Cells(i, 33)).Value = _
GlActuals.Range(GlActuals.Cells(j, 5), GlActuals.Cells(j, 16)).Value

End If
j = j + 1 ‘incriment j to continue loop

Loop

‘Restart processes
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
The code breaks when i = 709 and j = 464 during the ‘Find Match between Fcst and GL if statement.

Any second eyes on this would be appreciated, I’m probably missing something small I just can’t find it.


Answer:

Read more

Leave a Reply

Your email address will not be published. Required fields are marked *