fill down to next change in value

[Gauthier]

(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

 

[Rog]

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

 

[Gauthier]

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]

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

 

[Gauthier]

Hi Rog...many thank you's...c1 contained a heading, which i changed to a
number value...and it works fine now!
HAVE A GREAT DAY!
sandi

 

[Gauthier]

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

 

[Rog]

Ok, the number is too big for a long data type. We'll just
change it to a double :

Dim rngCell As Range
Dim dblLastNum As Double

Set rngCell = Range("C1")

dblLastNum = Cdbl(rngCell.Value)

While Not IsEmpty(rngCell)
If IsNumeric(rngCell.Value) Then
dblLastNum = rngCell.Value
Else
rngCell.Value = dblLastNum
End If
Set rngCell = rngCell.Offset(1)
Wend


Rgds

Rog

 

[Gauthier]

oh, thank you AGAIN AND AGAIN!!
it worked!

Ok, the number is too big for a long data type. We'll just
change it to a double :

Dim rngCell As Range
Dim dblLastNum As Double

Set rngCell = Range("C1")

dblLastNum = Cdbl(rngCell.Value)

While Not IsEmpty(rngCell)
If IsNumeric(rngCell.Value) Then
dblLastNum = rngCell.Value
Else
rngCell.Value = dblLastNum
End If
Set rngCell = rngCell.Offset(1)
Wend


Rgds

Rog