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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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

On Feb 14, 10:00 am, Valeria
wrote:
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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

"Valeria" wrote in message
...
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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").Cell s(i, 9).Value
<Overflow Variant/Integer

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


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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

"Valeria" wrote in message
...
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").Cell s(i, 9).Value
<Overflow Variant/Integer

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


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 9).Value
<Overflow Variant/Integer

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


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria

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
--
Valeria


"Martin Fishlock" wrote:

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 9).Value
<Overflow Variant/Integer

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


"NickHK" wrote:

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

"Valeria" wrote in message
...
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").Cell s(i, 15) =
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6) *
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9)
Next i

How do I declare
ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(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,
--
Valeria