Code to Count specific occurances in a sheet?

[NessPJ]

Hi all,

I'm looking for a piece of code that will count the number of specified occurances in one of my sheets.

I hope the following explanation will suffice.....

My sheet contains a lot of data like:
Column A: Date
Column B: Time
Column C: Error code
Column D: A formula to show a "1" if a specific error code occurs between 0:00 and 08:00u.
Column E: A formula to show a "1" if a specific error code occurs between 8:01 and 16:00u.

Now, what i want to do is...count the number of 1's in column D and E for a specific Date.
This date is retreived from another cell in the workbook.

 

[Jameo]

Ok, no problem.

Pick the cell where you want to define the date, then below

"lRow = Range("A" & Rows.Count).End(xlUp).Row + 1"

add:

nRow = Cells(3, 34)

Where 3,34 is the cell reference. i.e this would be AH,3 (Change accordingly where ever you want to place the date.

Then in every if statement add:

And Cells(i, 1) = nRow

i.e.

If Cells(i, 23).Value = "1" Then
Er6 = Er6 + 1
End If

becomes:

If Cells(i, 23).Value = "1" And Cells(i, 1) = nRow Then
Er6 = Er6 + 1
End If


Make sure you change every statement.

That should work.

You should also change

Dim lRow, nRow As Long

to:

Dim lRow as Long
Dim nRow as Date

 

[NessPJ]

Hey there,

Thanks a million for all the help!
I fiddled around a little with the code and its working now!

One more question however...
Would it be possible to refer to a Date in a Cell that is not on the same Worksheet?

Will i have to use:
nRow = Sheets.("Sheet1").Cells(3, 34) ?