OOPS! b/o error - fill down to next change in value
hi rog - me again...
oh oh...i've been making some changes, and now i get a BUFFER OVERFLOW ERROR
when it gets to line 5 ??
-----------------------------------------------------------------------
debugging shows the following values for;
lngLastNum = 42010101
CLng(rngCell.Value) = 4201010101
-----------------------------------------------------------------------
Cell C1 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
"Rog" wrote in message
...
It's trying to convert the value in cell C1 to a "long"
numeric type. Change C1 to the first cell in column C
which contains a number, and it shoud be fine
Rgds
Rog
-----Original Message-----
Hi Rog - thanks for your reply
pls pardon my lack of vba knowledge...
i copied the code beginning with Dim rngCell As
Range....to...Wend into my
current sub module...is that OK?
then i ran the code and rec'd a "TYPE MISMATCH" error at
the following
execution:
lngLastNum = CLng(rngCell.Value)
not sure what it means or how to correct...
sandi
"Rog" wrote in
message
...
Sandi, this should do what you want, as long as there
are
no empty cells in column C
Sub c()
Dim rngCell As Range
Dim lngLastNum As Long
Set rngCell = Range("C1")
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
End Sub
Rgds
Rog
-----Original Message-----
(i certainly hope there is no limit as to the number of
questions one can
ask! - so here we go again!)
below is a snipet of my database (note database is
currently at 1000 records
or so, and can vary in length):
COL A COL B COLUMN
C COLUMN D
-------------------------------------------------------
---
-----------------
SEQ: T ERRI 42010107
FALSE
200203 21 EXCHANGE RATE:
1.588 88,395.18
200204 22 EXCHANGE RATE:
1.582 89,873.48
200205 22 EXCHANGE RATE:
1.551 90,590.98
SEQ: T ERRI 42010108
FALSE
200203 21 EXCHANGE RATE:
1.588 88,395.18
200204 22 EXCHANGE RATE:
1.582 89,873.48
200205 22 EXCHANGE RATE:
1.551 90,590.98
SEQ: T ERRI 42010109
FALSE
200203 21 EXCHANGE RATE:
1.588 88,395.18
200204 22 EXCHANGE RATE:
1.582 89,873.48
200205 22 EXCHANGE RATE:
1.551 90,590.98
COLUMN C is currently formatted in "general" format...i
could change to
NUMBER format if req'd...
my goal is to replace the "exchange rate:....." text
with
the territory
numbers (ie..42010107, 42010108, 42010109)
i am looking for some code that will, for example,
- carry down the 42010107 until it reaches the next
number (ie..42010108)
- then carry down the next number (42010108) until it
reaches the next
number (42010109)
- then carry down the next number (42010109) until it
reaches the next
number
- until the end, where there is no values
i want to replace the text "EXCHANGE RATE:" with the,
in
this case,
territory numbers (eg. 42010107, 42010108, 42010109)
i only hope i am explaining this in a manner with which
you seasoned
professionals can understand!!
oh - and keep in mind, my vba skills are at beginner
level at best!
appreciate any assistance you can provide...
thanks..sandi
.
.
|