Count every 15 Minutes

[shonaart]

Hi

Column A: Date & Time ( 23 Aug 2011 13:20 etc ) From row 1 to row 1000

I want a formula to count number of enteries every 15 Min.
So it will start with Row 1 ( date & time ) and count how many entries / Rows it is in every 15 Minutes .

Thanks
Vaughan

 

[Trevor G]

You would need to look at timer events.

I haven't tested this but it would be something like this

Sub count15()
'Application.OnTime Now + TimeValue("00:00:15"), "cellCount"
Application.OnTime Now + TimeValue("00:15:00"), "cellCount"
End Sub
Sub CellCount()
Dim lngCnt As Long
Worksheets("Sheet1").Select
Range("a1").Select
Do Until ActiveCell.Value = ""
If ActiveCell > 0 Then
lngCnt = lngCnt + 1
End If

Cells(1, 4).Value = lngCnt
Loop
Range("a1").Select
End Sub

 

[shonaart]

Hi Trevor,
Thanks so much for the help. But a stupid question , how do add that to my worksheet.
I can add a formula, not sure how do this one,
Thanks

 

[Trevor G]

As this is VBA and it may seem as though you aren't to sure what to do and where to place it, lets see what we can do.

Take a copy of the workbook and we will place this in the copy to see if it works.

Open the copy and then use Alt + F11 on the keyboard this will take you into Excel VBA and then from the Inser Menu select Module. Then copy the second amount of code I palced in.

Next on the left you are looking for something called ThisWorkBook and you have to double click here to open another screen (another module sheet) at the top of the screen you will see 2 drop downs and you need to select General and change to Workbook, this will provide you with an Event for On Open, then you copy the first part of the code in here.

So the idea is that when the workbook opens it will start the timer event and every 15 minutes it will then count through the range of cells and give you a total.

I believe that is what you want.