getting an overflow error msg when running macro

[kskinne]

when i run the following macro:

Sub test()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 9 To LastRow
Cells(i, 4).Value = Cells(i, 5).Value / Cells(i, 3).Value
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

i get the error message "Run-time error '6': Overflow"

when it hits the line:

Cells(i, 4).Value = Cells(i, 5).Value / Cells(i, 3).Value

the range of data i am running this macro on is about 20,000 rows of data that is pulled from an access database using a query. the code is erroring out on about row #11,802

anyone know how to avoid this?

thanks,
kevin

 

[just_jon]

You must Dim your counter variable i as long --

Dim i as Long

*Always* good practice... as is Option Explicit...

 

[Mudface]

Presumably that cell has a zero in it, check the cells with an If... Then statement around it before you do your calculation.

 

[TommyGun]

Tested this....a 0 creates a division by zero error, not overflow error.

Here's one check from the almighty Microsoft KBase articles...

http://support.microsoft.com/default.aspx?scid=kb;en-us;299846

Here's another one....seems like you aren't the first person to have this problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;125900

 

[kskinne]

still getting the error msg

I tried declaring my variable as Long and am still getting the same error message

 

[Mudface]

Try -

MsgBox 0/0



Check your cells first, then do the calculation.

 

[TommyGun]

Chris, I'm not trying to argue, but plug 0 in A1, 1 in B1, and then run this code...

Sub checkoverflow()
Cells(1, 3).Value = Cells(1, 2).Value / Cells(1, 1).Value
End Sub

And then run this code and watch how the same "values" produce a different error message...

Sub checkoverflow2()

cell1 = Cells(1, 1).Value
cell2 = Cells(1, 2).Value
cell3 = cells2 / cell1

Cells(1, 3).Value = cell3

End Sub

Another wonderful perc of XL.

 

[Mudface]

I know, but make both A1 and B1 = 0 and you'll get the overflow error...

 

[TommyGun]

I know, but make both A1 and B1 = 0 and you'll get the overflow error...

Sorry! Missed that part of the equation. My question remains, why does XL return a different error message for the same calculation (ie two codes listed above)?

 

[TommyGun]

Hey kskinne, didn't forget about you. Try this and see if it works...

Sub test()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 9 To LastRow
If Cells(i,5).Value <> 0 And Cells(i,3).Value <> 0 Then Cells(i, 4).Value = Cells(i, 5).Value / Cells(i, 3).Value
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub