Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 863
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 863
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 863
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Overflow error.. why? Fingerjob Excel Discussion (Misc queries) 4 November 13th 06 05:18 PM
runtime error '6' overflow don Setting up and Configuration of Excel 1 July 26th 05 02:52 AM
Overflow Error DG Excel Discussion (Misc queries) 3 April 15th 05 05:45 PM
Run-time error '6' overflow Suzy[_2_] Excel Programming 2 February 13th 04 04:55 PM
overflow error ExcelMonkey[_5_] Excel Programming 6 January 22nd 04 02:34 AM


All times are GMT +1. The time now is 08:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"