how to make it count correctly?

[robsonsquest]

this loop should count every event in the G column that meets the criteria. When the entry in the E column meets the exact day and 0<x<70000 criteria and the entry in the G column meets the <=40000 criteria the countsno4 should be incremented, and it is, but not correctlly. any sugdestions how to correct it?

For Each cell In WS.Range("E2", Range("E1000").Address)

If cell.Value <> "" And Len(cell.Value) > 0 Then

convert = Format(cell.Value, "yyyy-mm-dd hh:mm:ss")

DateofEntry = Left(convert, 19)
TimeofEntry = Replace((Right(convert, 9)), ":", "")

convert = Format(cell.Offset(0, 2).Value, "hh:mm:ss")
start = Replace(convert, ":", "")

If Weekday(DateofEntry, vbMonday) < 6 And TimeofEntry >= 0 And TimeofEntry <= 70000 And start <= 40000 Then countsno4 = countsno4 + 1

Else: End If

Next cell

[stevebriz]

your date of entry will give you nothing
DateofEntry = Left(convert, 19)

try
DateofEntry = Left(convert, 10)

[robsonsquest]

hi Steve! you probably noticed its a modified version of macro you've helped me to create a while ago. the DateofEntery works just fine. the problem is in the incrementation of the countsno4. it counts to much. I have 54 enteries but it counts 92...

[stevebriz]

Are you able to email me you xls ...so I can find the problem?

[robsonsquest]

hi there. i managed to make the loop to work correctly. now the only problem is

For Each cell In WS.Range("E2", Range("E1000").Address)

line. i need to make this loop count every cell in the E column that has an entry, not only the fixed range (2-1000). Any ideas?

[stevebriz]

Do you jsut want to do it to the last used cell in the column? then use this:

Please Login or Register  to view this content.

[robsonsquest]

it doesn't work wright. in the next loop (the same, only for another criteria) it starts from the E1 cell and then I get a type mismatch error.

[stevebriz]

can you email me the xls?

[robsonsquest]

sorry for my late reply. I've used the most sinple soulution:

next13:
For Each cell In WS.Range("E2", Range("E10000").Address)
'all events

If cell.Value <> "" And Len(cell.Value) > 0 Then

countall = countall + 1

Else: GoTo next14
End If
Next cell

next14:
For Each cell In WS.Range("G2", Range("G10000").Address)
'all events <= 4h

If cell.Value <> "" And Len(cell.Value) > 0 Then

conventry = Format(cell.Value, "hh:mm:ss")
Duration = Replace(conventry, ":", "")

If Duration <= 40000 Then countall4 = countall4 + 1

Else: GoTo next15
End If
Next cell

next15:

the macro jumps out of the loop with the "go to" instruction when the next checked cell is empty. Thanks for all Your help!