Hi All,
I need some help with the below code. What this does is take a date range, and count the number of dates, but groups consecutive dates into 'spells', for example:
01/05/2008 1
02/05/2008 1
06/05/2008 2
09/05/2008 3
11/05/2008 4
12/05/2008 4
This would be counted as 4 'spells', which is what I want. But what I need the macro to do is to run through a list of people's names, get to the last name, offset 2 columns, and place the number of spells there:
Hope this makes sense!!
I need some help with the below code. What this does is take a date range, and count the number of dates, but groups consecutive dates into 'spells', for example:
01/05/2008 1
02/05/2008 1
06/05/2008 2
09/05/2008 3
11/05/2008 4
12/05/2008 4
This would be counted as 4 'spells', which is what I want. But what I need the macro to do is to run through a list of people's names, get to the last name, offset 2 columns, and place the number of spells there:
Rich (BB code):
Name: Date: Spells:
Charlie 01/04/2008
Charlie 02/04/2008
Charlie 03/04/2008
Charlie 06/04/2008 2
Mike 03/04/2008
Mike 04/04/2008
Mike 18/04/2008
Mike 20/04/2008
Mike 21/04/2008
Mike 29/04/2008 4
Rich (BB code):
Sub spells()
Dim i As Integer
Range("E1").Select
i = 1
Do Until ActiveCell = ""
If ActiveCell = "" Then
GoTo 10
Else
ActiveCell.Offset(1, 0).Select
If ActiveCell = ActiveCell.Offset(-1, 0) + 1 Then
i = i
Else
i = i + 1
End If
End If
Loop
10
Range("H2").Select
ActiveCell = i - 1
End Sub