VBA to Count the Rows between Occurrences of a Specific Value

[DavidWF]

I have a worksheet with several thousand rows of data. Column C contains the letters SL at irregular intervals down the column; each cell in the data section of col C is either blank or contains the letters SL. Data starts in row 8. I have two requirements:
1. I need to count the number of rows between each occurrence of the letters SL within the range D8:D20,000; and
2. I need that number entered in col D, in the cell adjacent to each occurrence of SL in col C.
I can then filter col D and use Subtotal to see how many times the occurrences of SL are 2 rows apart, 3 rows apart, 4 rows apart . . . . and so on.
I've scrolled down col C and have been unable to see more than 15 rows between occurrences to date. SL can never occur in two adjoining rows, so a loop that counted from 2 to 15 should be OK using current data. It would help though to increase the loop to allow for 20 rows between occurrences, in case future data has less frequent occurrences.
The following shows an example of how the worksheet would look.
VBA guidance that enables me to achieve this would be greatly appreciated.
[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Event[/TD]
[TD]Interval[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]12-Jan[/TD]
[TD]0000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]12-Jan[/TD]
[TD]0100[/TD]
[TD]SL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12-Jan[/TD]
[TD]0200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]12-Jan[/TD]
[TD]0300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12-Jan[/TD]
[TD]0400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12-Jan[/TD]
[TD]0500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12-Jan[/TD]
[TD]0600[/TD]
[TD]SL[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]12-Jan[/TD]
[TD]0700[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]12-Jan[/TD]
[TD]0800[/TD]
[TD]SL[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]12-Jan[/TD]
[TD]0900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]12-Jan[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]12-Jan[/TD]
[TD]1100[/TD]
[TD]SL[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]12-Jan[/TD]
[TD]1200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]12-Jan[/TD]
[TD]1300[/TD]
[TD]SL[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

Sub MG13May11
Dim Rng As Range, Dn As Range, c As Long
Set Rng =  Range("C8:C20000")
For Each Dn In Rng
    c = c + 1
    If Dn.Value = "SL" Then
        Dn.Offset(, 1).Value = c: c = 0
    End If
Next Dn
End Sub

Regards Mick

 

[DavidWF]

Brilliant! Works perfectly.

Thanks so much for responding so promptly Mick.

 

[MickG]

You're welcome