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

 

[Mudface]

I guess it's a bit of a semantic point, but in the first one you're doing a calculation and assigning it to a property of an object (the worksheet range's value) and in the second you're trying to assign the outcome of the calculation to a variable. Doesn't seem entirely consistent :D .

 

[TommyGun]

I totally agree, however the underlying value that is being calculated is the same. The method is different, and I guess this is what is causing the differing error messages, but it still doesn't make sense to me.

Oh, and I voted for Mudface, but Chris Howarth does sound more professional.

 

[kskinne]

no luck

hi and thanks for the help, however i'm still getting the error message - none of the rows in either column in question contains a zero value, and it never will due to the type of data i am pulling. could this have something to do with trying to used two different data types in the formula? this was mentioned in a microsoft KB link in one of the previous posts...

 

[TommyGun]

I tested the code that was provided on 38,000+ rows of data, and it worked fine. Can you post some sample data using Colo's utility?

 

[kskinne]

here you go

LD USAGE.xls
	A	B	C	D	E
8	Date/Time	AcctNumber	Minutes	Rate	Cost
9	04/23/02	7127924572	14.8	0.0865	1.2802
10	04/26/02	7127924572	15	0.0865	1.2975
11	04/20/02	8885338324	1.1	0.0865	0.0952
12	04/20/02	8006682398	0.9	0.0865	0.0779
13	04/20/02	7128225433	9.6	0.0865	0.8304
14	04/20/02	7123858531	0.5	0.0865	0.0263
15	04/20/02	7126732802	0.5	0.0865	0.0433
16	04/20/02	6414393265	10.5	0.0865	0.9083
17	04/20/02	6414392393	1.7	0.0865	0.1471
18	04/20/02	8006682398	0.5	0.0865	0.0433
19	04/20/02	6414393265	7.3	0.0865	0.6315
20	04/20/02	6414393265	0.2	0.0865	0.0173
21	04/20/02	8006682398	0.6	0.0865	0.0519
22	04/20/02	6414393265	1	0.0865	0.85
23	04/20/02	8885338324	1.6	0.0865	0.1384
24	04/20/02	6414393265	1.4	0.0865	0.1211
25	04/20/02	7128225414	10.1	0.0865	0.8737
usage detail

 

[just_jon]

I'm sorry to have given you bad advice the first time. Try this --

Sub test()
    Dim i As Long, LastRow as Long
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = 9 To LastRow
        On Error Resume Next
        Cells(i, 4) = Cells(i, 5) / Cells(i, 3)
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Generates --

Book1
	C	D	E	F
9	1	3	3
10	2	2	4
11
12	3	0
13	4	0.25	1
Sheet1

 

[Mudface]

Before you do so, run the macro and when it errors out, enter Debug mode and click on the View- Locals Window. Make a note of what i is and check the appropriate cells and post their values.

 

[TommyGun]

Before you do so, run the macro and when it errors out, enter Debug mode and click on the View- Locals Window. Make a note of what i is and check the appropriate cells and post their values.

I agree. I just tested the code on 30,000 rows of data, and it ran fine with data similar to what your example showed. There must be some values inside your information that are causing this to error.

 

[kskinne]

i found it

when i checked the locals window, it showed the error occurred on row 11803, and sure enough the value of cell C11803 was zero. this numbers pulled from the database for this column are minutes of usage for call records, so there should never be a zero in this column. so i checked the database call record in my usage table, fixed the record that had the zero value, then re-ran my query, and ran the macro. it now works fine. so the culprit really was a zero value. thanks so much to everybody for your help - much appreciated. i can go home at 5:00 now

best regards
kevin