Counter for inserting sequence of numbers in Column C

[Mike]

Thank you in advance for any help.

I am working with an excel spreadsheet that has the
following data:

ColumnA ColumnB ColumnC
H 555555 01/12/2005
L 555555
D 555555
D 555555
D 555555
D 555555
D 555555
L 555555
D 555555
D 555555
L 555555
D 555555
D 555555
L 555555
D 555555

So far I have written the following macro:
Range(Selection, Selection.End(xlDown)).Select
Dim L
For L = 1 To 250
Selection.Find(What:="L", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
On Error Resume Next
Do Until ActiveCell = "D"
ActiveCell.Offset(0, 2).Select
ActiveCell = "1"
ActiveCell.Offset(1, -2).Select
Range(Selection, Selection.End(xlDown)).Select
Loop
Next L

This macro produces the following which I will refer to as
Example A
ColumnA ColumnB ColumnC
H 555555 01/12/2005
L 555555 1
D 555555
D 555555
D 555555
D 555555
D 555555
L 555555 1
D 555555
D 555555
L 555555 1
D 555555
D 555555
L 555555 1
D 555555

I want the end result to look like the example B below. I
can't use Offsets because the number of Ds after each L
changes.

Example B
ColumnA ColumnB ColumnC
H 555555 01/12/2005
L 555555 1
D 555555 1
D 555555 1
D 555555 1
D 555555 1
D 555555 1
L 555555 2
D 555555 2
D 555555 2
L 555555 3
D 555555 3
D 555555 3
L 555555 4
D 555555 4

Does anyone know how I could alter my code to produce
Example B instead of Example A? Thanks again for your
help. I am using Excel 2000.

Mike

 

[Tom Ogilvy]

Dim rng as Range
Dim rng1 as Range
Dim rng2 as Range

set rng1 = Nothing
Range(Selection, Selection.End(xlDown)).Select
Dim L
For L = 1 To 250
Selection.Find(What:="L", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
if rng is nothing then _
set rng = ActiveCell
On Error Resume Next
Do Until ActiveCell = "D"
ActiveCell.Offset(0, 2).Select
ActiveCell = L
ActiveCell.Offset(1, -2).Select
Range(Selection, Selection.End(xlDown)).Select
Loop
Next L
set rng = Range(rng, cells(rows.count,rng.column).End(xlup))
set rng = rng.offset(0,2)
set rng 2 = rng
set rng1 = rng(1)
set rng = rng.specialCells(xlBlanks)
rng.Formula = "=" & rng1.Address(0,0)
rng2.formula = rng2.Value

Code is untested and may contain typos.

 

[Mike]

Tom -
That code works perfectly. Thanks again for your help.
Mike