| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Overflow Error 6
oh oh...i rec'd this code from another valuable contributor and it worked
fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
From Help:
"Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647" 4,201,010,101 is, as the error message told you, too big for a long integer. You need to use the Double data type here. On Thu, 23 Sep 2004 22:14:23 -0400, "Gauthier" wrote: oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
Gauthier,
Check the help files for "long" and see what it's maimum value is. NickHK "Gauthier" wrote in message ... oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
Hi Myrna...thanks for your reply! i must pre-empt with my usual, "my vba
skills are beginner - at best" spiel!... pardon my ignorance, but what is "double" data type, and would i go about incorporating that into my code?? (sorry for asking) sandi "Myrna Larson" wrote in message ... From Help: "Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647" 4,201,010,101 is, as the error message told you, too big for a long integer. You need to use the Double data type here. On Thu, 23 Sep 2004 22:14:23 -0400, "Gauthier" wrote: oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
Change this line Dim lngLastNum As Long
To Dim lngLastNum As Double On Thu, 23 Sep 2004 23:07:01 -0400, "Gauthier" wrote: Hi Myrna...thanks for your reply! i must pre-empt with my usual, "my vba skills are beginner - at best" spiel!... pardon my ignorance, but what is "double" data type, and would i go about incorporating that into my code?? (sorry for asking) sandi "Myrna Larson" wrote in message .. . From Help: "Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647" 4,201,010,101 is, as the error message told you, too big for a long integer. You need to use the Double data type here. On Thu, 23 Sep 2004 22:14:23 -0400, "Gauthier" wrote: oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
BTS, since this code uses variable prefixes, to be consistent, you should
change the variable name to dblLastNum, too. If you do that, do it with Search and Replace, so you don't miss any occurrences. On Thu, 23 Sep 2004 23:05:45 -0500, Myrna Larson wrote: Change this line Dim lngLastNum As Long To Dim lngLastNum As Double On Thu, 23 Sep 2004 23:07:01 -0400, "Gauthier" wrote: Hi Myrna...thanks for your reply! i must pre-empt with my usual, "my vba skills are beginner - at best" spiel!... pardon my ignorance, but what is "double" data type, and would i go about incorporating that into my code?? (sorry for asking) sandi "Myrna Larson" wrote in message . .. From Help: "Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647" 4,201,010,101 is, as the error message told you, too big for a long integer. You need to use the Double data type here. On Thu, 23 Sep 2004 22:14:23 -0400, "Gauthier" wrote: oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help! Overflow Error 6
You might also want to change
lngLastNum = CLng(rngCell.Value) to just lngLastNum = rngCell.Value -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... Change this line Dim lngLastNum As Long To Dim lngLastNum As Double On Thu, 23 Sep 2004 23:07:01 -0400, "Gauthier" wrote: Hi Myrna...thanks for your reply! i must pre-empt with my usual, "my vba skills are beginner - at best" spiel!... pardon my ignorance, but what is "double" data type, and would i go about incorporating that into my code?? (sorry for asking) sandi "Myrna Larson" wrote in message .. . From Help: "Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647" 4,201,010,101 is, as the error message told you, too big for a long integer. You need to use the Double data type here. On Thu, 23 Sep 2004 22:14:23 -0400, "Gauthier" wrote: oh oh...i rec'd this code from another valuable contributor and it worked fine at first, however, the data changed, and now i get a OVERFLOW ERROR 6 when it gets to line 5 ?? the code fills down the numeric value, until it reaches the next value, then takes THAT value, and fillsdown until the next value, and so on, and so on... ----------------------------------------------------------------------- debugging shows the following values for; lngLastNum = 42010101 CLng(rngCell.Value) = 4201010101 ----------------------------------------------------------------------- Cell C1 (where it starts) has the following value: 4201 the next value encountered is: 42010101 - and it fills down correctly next value encountered is: 4201010101 - that's when the b/o error occurs ------------------------------------------------------------------------ ACTUAL CODE line 1 ' FILLDOWN TERRITORY NUMBERS 2 Dim rngCell As Range 3 Dim lngLastNum As Long 4 Set rngCell = Range("C1") 5 lngLastNum = CLng(rngCell.Value) While Not IsEmpty(rngCell) If IsNumeric(rngCell.Value) Then lngLastNum = rngCell.Value Else rngCell.Value = lngLastNum End If Set rngCell = rngCell.Offset(1) Wend --------------------------------------------------------------- APPRECIATE YOUR ASSISTANCE - AS ALWAYS! Sandi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Overflow error.. why? | Excel Discussion (Misc queries) | |||
| runtime error '6' overflow | Setting up and Configuration of Excel | |||
| Overflow Error | Excel Discussion (Misc queries) | |||
| Run-time error '6' overflow | Excel Programming | |||
| overflow error | Excel Programming | |||
Linear Mode
