Sequence of numbers
Sorry, there were 2 variables you don't need.
Sub CountDates()
Dim LRow As Long
Dim c As Range, Lst As Range
LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)
For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub
Mike F
"Mike Fogleman" wrote in message
m...
This will work on any size range:
Sub CountDates()
Dim LRow As Long, i As Long, j As Long
Dim c As Range, Lst As Range
LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)
For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub
Mike F
"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of numbers
against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column B.
To show you what I mean in a little more detail:
Date column Sequence column
Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1
Mon 24 Jul 1
Mon 24 Jul 2
I hope I explained that well enough.
Many thanks for your help
John
|