Re: overflow problem and variable declaration

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"paul.robinson@xxxxxxxxxxxxxx" wrote:

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

On Feb 14, 12:43 pm, Valeria <Vale...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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" <Vale...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9EE4215-E4F1-4338-8FB9-DCD6553FE4EC@xxxxxxxxxxxxxxxx
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
--
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" <Vale...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1FC8339B-739B-4981-86BE-3E06EEDF6A03@xxxxxxxxxxxxxxxx
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,
--
Valeria- Hide quoted text -

- Show quoted text -



.

Quantcast