overflow problem and variable declaration

[Guest]

Dear experts,
I am getting an overflow error type because I am not able to declare my
variables the way they should be (ex long).
I have a code like:
For i=2 to LastRow
ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 9)
Next i

How do I declare ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 9)
to be long and not taken as Variant.Integer which is the way Excel handles it
today?

Many thanks in advance for your help.
Best regards,

 

[paul.robinson]

Hi
I can't see a problem with your code. I can't see that you are
declaring a variable with a Dim statement. Maybe there is a problem
somewhere else in your code? - the positioning of error messages is
not always reliable.
You might shorten your code a bit

Dim lastRow as long, i as long
With ActiveWorkbook.Worksheets("Complete_PM_List")
For i=2 to LastRow
.Cells(i, 15) = .Cells(i, 6) * .Cells(i, 9)
Next i
End With

regards
Paul

 

[NickHK]

Valeria,
What makes you think it is an Integer ? Using the immediate window, I get:

range("a6").Value=cint(100)
?typename(range("a6").Value)
Double

I suspect your error is elsewhere.
What is the value of LastRow ?

Also, maybe this is easier to read:

With ActiveWorkbook.Worksheets("Complete_PM_List")
.Cells(i, 15).Value =.Cells(i, 6).Value *.Cells(i, 9).Value
End With

NickHK

 

[Guest]

Hi,
one of the cells that is being counted has a value of -9*10^15, which is
what is giving me the overflow.
I can see it by using the "watch" window:
ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 9).Value
<Overflow> Variant/Integer

Could you please help me with this?
Thanks,
Best regards

 

[NickHK]

Valeria,
I cannot reproduce your error, with XL2002.
In the watch window, I see a value of -9E+15, with a Type of Variant/Double.

NickHK

 

[Guest]

Hi Valeria:

Try inserting the following in your code at the start

dim i as long

and se if that fixes it.

If no luck try the follwoing:

convert the numbers to doubles with cdbl() I also put a with in to make it
easier to understand.

With ActiveWorkbook.Worksheets("Complete_PM_List")
For i=2 to LastRow
.Cells(i, 15) = cdbl(.Cells(i, 6)) * cdbl(.Cells(i, 9))
Next i
end with

This cdbl wit convert the numbers to doubles.

 

[Guest]

Hello,
unfortunately none of this works. The error is generated before the code
arrives at cdbl,so before the actual calculation.
I am using Excel 2003 if this might help and it causes me the error even
when using only the code I have written below in a blank module.
The calculation that excel has to perform is ($90,893,315,088,973,000)*
3.30035272347974E-15

Thank you!
Kind regards

 

[paul.robinson]

Hi
Excel isn't really built for high precision arithmetic. That said, try
removing the formatting on your cells (the calculation may not like
the $ or commas) and then try it. Put formatting back on the answer
afterwards.
regards
Paul

 

[Guest]

Hi if you like send me the workbook and I will have a look.

martin_fishlock @ yahoo.co.uk.removethis

What is the value of lastrow?

you could also try using a variable as in

dim i as long
dim d1 as double, d2 as double, d3 as double
With ActiveWorkbook.Worksheets("Complete_PM_List")
For i=2 to LastRow
d1 = cdbl(.Cells(i, 6))
d2 = cdbl(.Cells(i, 9))
d3 = d1 * d2
.Cells(i, 15) = d3
Next i
end with

and that will show where the error is occuring.

 

[Guest]

This was it, thank you very much!
The formatting was obliging the variant to be integer (=currency) and
therefore I was having the overflow problem.

Thank you very much to you all for all useful tips and to Paul for solving
the problem.
Kind regards

 

[Dave Peterson]

If the format was currency, you could modify your code to use .value2 (instead
of .value, the default property you relied on).

For i=2 to LastRow
ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 15).value2 _
= ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 6).value2 _
* ActiveWorkbook.Worksheets("Complete_PM_List").Cells(i, 9).value2
Next i

This was it, thank you very much!
The formatting was obliging the variant to be integer (=currency) and
therefore I was having the overflow problem.

Thank you very much to you all for all useful tips and to Paul for solving
the problem.
Kind regards