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
--
Regards,
Tom Ogilvy
"martinrrrr - ExcelForums.com"
wrote in message ...
A.............B................C
1..Inv.......Log
2..123......4444
3.............4567
4.............5678
5..456......1275
6.............5414
7.............8513
8.............9999
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. C
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 in
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 C
1..Inv......Log........Line#
2..123.....4444.......001
3............4567.......002
4............5678.......003
5..456.....1275.......001
6............5414.......002
7............8513.......003
8............9999.......004
Thanks for any help.
Roger
---------
Message sent via www.excelforums.com