Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 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" 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- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Valeria" 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" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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 " 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 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" 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- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
overflow problem and variable declaration
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").Cell s(i, 15).value2 _ = ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 6).value2 _ * ActiveWorkbook.Worksheets("Complete_PM_List").Cell s(i, 9).value2 Next i Valeria wrote: 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 " 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 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" 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- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - variable declaration | Excel Discussion (Misc queries) | |||
External Variable Declaration | Excel Programming | |||
External Variable Declaration | Excel Programming | |||
Global variable declaration! | Excel Programming | |||
Variable Declaration?? | Excel Programming |