sequential number based another cell

[martinrrrr - ExcelForums.com]

A.............B................
1..Inv.......Lo
2..123......444
3.............456
4.............567
5..456......127
6.............541
7.............851
8.............999

I get a pivot table report like the above

I convert it to a static table and I want to add a Line# in Col.
like shown in cell C2. It will just be a sequential number like 1, 2
3, etc and will based off of the entry in Col A. When the number i
Col A changes I want the numbers in Col. C to start over again, 1, 2
3 etc. as in the table below

A B
1..Inv......Log........Line
2..123.....4444.......00
3............4567.......00
4............5678.......00
5..456.....1275.......00
6............5414.......00
7............8513.......00
8............9999.......00

Thanks for any help

Roge

 

[Tom Ogilvy]

Sub CCC()
Dim rng As Range, rng1 As Range, i As Long
Dim ar As Range
Set rng = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
Set rng1 = rng.Offset(0, -1).SpecialCells(xlBlanks)
For Each ar In rng1.Areas
ar(0, 3).Value = "'" & "001"
i = 1
For Each cell In ar
i = i + 1
cell.Offset(0, 2).Value = "'" & Right("000" & i, 3)
Next cell
Next ar
Set rng1 = Nothing
On Error Resume Next
Set rng1 = rng.Offset(0, 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Value = "'001"
End If
End Sub

 

[martinrrrr - ExcelForums.com]

It works great

Thanks again Tom