Help! Overflow Error 6

[Gauthier]

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

 

[Myrna Larson]

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.

 

[NickHK]

Gauthier,
Check the help files for "long" and see what it's maimum value is.

NickHK

 

[Gauthier]

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

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.

 

[Myrna Larson]

Change this line Dim lngLastNum As Long

To Dim lngLastNum As Double

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

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.

 

[Myrna Larson]

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.

Change this line Dim lngLastNum As Long

To Dim lngLastNum As Double

 

[Tom Ogilvy]

You might also want to change

lngLastNum = CLng(rngCell.Value)

to just
lngLastNum = rngCell.Value

--
Regards,
Tom Ogilvy

Change this line Dim lngLastNum As Long

To Dim lngLastNum As Double