fill down to next change in value

  • Thread starter Thread starter Gauthier
  • Start date Start date
G

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
 
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
Click to expand...
C COLUMN D
 
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
 
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
 
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
 
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
 
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
 
oh, thank you AGAIN AND AGAIN!!
it worked!

Rog said:
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
Click to expand...
 
Back
Top