Hi All

What would be the best approach for the following scenario:

So much to learn, such a large vacuum of space to fill, I still have so much
to learn

I need help constructing the correct syntax

Sub TrafficFlow()

Dim myRng As Range

Set myRng = [F4:F300]

If myRng.Value = #12:00:00 AM# And myRng.Value < #1:00:00 AM# Then
Range("M5") = Count(myRng)
End If
If myRng.Value = #1:00:00 AM# And myRng.Value < #2:00:00 AM# Then
Range("M6") = Count(myRng)
End If
'
'
'down to

If myRng.Value = #11:00:00 PM# And myRng.Value < #12:00:00 AM# Then
Range("M28") = Count(myRng)
End If
End Sub

As always thank you so much for your assistance.

TIA
Mick.

Have a look at vba help index for SELECT CASE

On Jun 21, 6:59*am, "Vacuum Sealed" wrote:
Hi All

What would be the best approach for the following scenario:

So much to learn, such a large vacuum of space to fill, I still have so much
to learn

I need help constructing the correct syntax

Sub TrafficFlow()

Dim myRng As Range

Set myRng = [F4:F300]

* * * * If myRng.Value = #12:00:00 AM# And myRng.Value < #1:00:00 AM# Then
* * * * * * Range("M5") = Count(myRng)
* * * * *End If
* * * * If myRng.Value = #1:00:00 AM# And myRng.Value < #2:00:00 AM# Then
* * * * * * Range("M6") = Count(myRng)
* * * * *End If
'
'
'down to

* * * * If myRng.Value = #11:00:00 PM# And myRng.Value < #12:00:00 AM# Then
* * * * * * Range("M28") = Count(myRng)
* * * * *End If
End Sub

As always thank you so much for your assistance.

TIA
Mick.

Thx for your reply Don

I was tossing up if to use SELECT, but was struggling with the correct way
to get the Count() incorporated into it.

I had
Select Case myRng
myRng.Value = #11:00:00 PM# And myRng.Value < #12:00:00 AM#
Case Is Range("M28") = Count(myRng)

Hence the need for outside intervention as I am slow to pick up on the many
variations in code structure.

Thx again.
Mick.

Don

I tried the Select this way, of course it did not work...

Sub TrafficFlow()

Dim i As Integer

For i = 4 To 300

Select Case Cells(i, 6).Count

Case Is = #12:00:00 AM# < #1:00:00 AM#: Range("M5") = Cells(i, 6).Count
Case Is = #1:00:00 AM# < #2:00:00 AM#: Range("M6") = Cells(i, 6).Count
Case Is = #2:00:00 AM# < #3:00:00 AM#: Range("M7") = Cells(i, 6).Count
Case Is = #3:00:00 AM# < #4:00:00 AM#: Range("M8") = Cells(i, 6).Count
Case Is = #4:00:00 AM# < #5:00:00 AM#: Range("M9") = Cells(i, 6).Count
Case Is = #5:00:00 AM# < #6:00:00 AM#: Range("M10") = Cells(i, 6).Count
Case Is = #6:00:00 AM# < #7:00:00 AM#: Range("M11") = Cells(i, 6).Count
Case Is = #7:00:00 AM# < #8:00:00 AM#: Range("M12") = Cells(i, 6).Count
Case Is = #8:00:00 AM# < #9:00:00 AM#: Range("M13") = Cells(i, 6).Count
Case Is = #9:00:00 AM# < #10:00:00 AM#: Range("M14") = Cells(i,
6).Count
Case Is = #10:00:00 AM# < #11:00:00 AM#: Range("M15") = Cells(i,
6).Count
Case Is = #11:00:00 AM# < #12:00:00 PM#: Range("M16") = Cells(i,
6).Count
Case Is = #12:00:00 PM# < #1:00:00 PM#: Range("M17") = Cells(i,
6).Count
Case Is = #1:00:00 PM# < #2:00:00 PM#: Range("M18") = Cells(i, 6).Count
Case Is = #2:00:00 PM# < #3:00:00 PM#: Range("M19") = Cells(i, 6).Count
Case Is = #3:00:00 PM# < #4:00:00 PM#: Range("M20") = Cells(i, 6).Count
Case Is = #4:00:00 PM# < #5:00:00 PM#: Range("M21") = Cells(i, 6).Count
Case Is = #5:00:00 PM# < #6:00:00 PM#: Range("M22") = Cells(i, 6).Count
Case Is = #6:00:00 PM# < #7:00:00 PM#: Range("M23") = Cells(i, 6).Count
Case Is = #7:00:00 PM# < #8:00:00 PM#: Range("M24") = Cells(i, 6).Count
Case Is = #8:00:00 PM# < #9:00:00 PM#: Range("M25") = Cells(i, 6).Count
Case Is = #9:00:00 PM# < #10:00:00 PM#: Range("M26") = Cells(i,
6).Count
Case Is = #10:00:00 PM# < #11:00:00 PM#: Range("M27") = Cells(i,
6).Count
Case Is = #11:00:00 PM# < #12:00:00 AM#: Range("M28") = Cells(i,
6).Count

End Select

Next

End Sub


Thx again...

Mick.

On Jun 21, 8:25*am, "Vacuum Sealed" wrote:
Don

I tried the Select this way, of course it did not work...

Sub TrafficFlow()

Dim i As Integer

For i = 4 To 300

* Select Case Cells(i, 6).Count

* * Case Is = #12:00:00 AM# < #1:00:00 AM#: Range("M5") = Cells(i, 6).Count
* * Case Is = #1:00:00 AM# < #2:00:00 AM#: Range("M6") = Cells(i, 6).Count
* * Case Is = #2:00:00 AM# < #3:00:00 AM#: Range("M7") = Cells(i, 6).Count
* * Case Is = #3:00:00 AM# < #4:00:00 AM#: Range("M8") = Cells(i, 6).Count
* * Case Is = #4:00:00 AM# < #5:00:00 AM#: Range("M9") = Cells(i, 6).Count
* * Case Is = #5:00:00 AM# < #6:00:00 AM#: Range("M10") = Cells(i, 6).Count
* * Case Is = #6:00:00 AM# < #7:00:00 AM#: Range("M11") = Cells(i, 6).Count
* * Case Is = #7:00:00 AM# < #8:00:00 AM#: Range("M12") = Cells(i, 6).Count
* * Case Is = #8:00:00 AM# < #9:00:00 AM#: Range("M13") = Cells(i, 6).Count
* * Case Is = #9:00:00 AM# < #10:00:00 AM#: Range("M14") = Cells(i,
6).Count
* * Case Is = #10:00:00 AM# < #11:00:00 AM#: Range("M15") = Cells(i,
6).Count
* * Case Is = #11:00:00 AM# < #12:00:00 PM#: Range("M16") = Cells(i,
6).Count
* * Case Is = #12:00:00 PM# < #1:00:00 PM#: Range("M17") = Cells(i,
6).Count
* * Case Is = #1:00:00 PM# < #2:00:00 PM#: Range("M18") = Cells(i, 6).Count
* * Case Is = #2:00:00 PM# < #3:00:00 PM#: Range("M19") = Cells(i, 6).Count
* * Case Is = #3:00:00 PM# < #4:00:00 PM#: Range("M20") = Cells(i, 6).Count
* * Case Is = #4:00:00 PM# < #5:00:00 PM#: Range("M21") = Cells(i, 6).Count
* * Case Is = #5:00:00 PM# < #6:00:00 PM#: Range("M22") = Cells(i, 6).Count
* * Case Is = #6:00:00 PM# < #7:00:00 PM#: Range("M23") = Cells(i, 6).Count
* * Case Is = #7:00:00 PM# < #8:00:00 PM#: Range("M24") = Cells(i, 6).Count
* * Case Is = #8:00:00 PM# < #9:00:00 PM#: Range("M25") = Cells(i, 6).Count
* * Case Is = #9:00:00 PM# < #10:00:00 PM#: Range("M26") = Cells(i,
6).Count
* * Case Is = #10:00:00 PM# < #11:00:00 PM#: Range("M27") = Cells(i,
6).Count
* * Case Is = #11:00:00 PM# < #12:00:00 AM#: Range("M28") = Cells(i,
6).Count

End Select

Next

End Sub

Thx again...

Mick.

Or without vba, just modify this formula to your needs. Sumproduct can
not use entire columns
Or, send me your file to

=SUMPRODUCT((B1:B21=1)*(B1:B21<10))

Don

Tried your suggestion

=SUMPRODUCT(($F$4:$F$300#12:00:01#)*($F$4:$F$300, <#1:00:01#))

No good.

Thx agian.
Mick.

On Jun 21, 9:17*am, "Vacuum Sealed" wrote:
Don

Tried your suggestion

=SUMPRODUCT(($F$4:$F$300#12:00:01#)*($F$4:$F$300, <#1:00:01#))

No good.

Thx agian.
Mick.

Where e20 & e21 are also times

'=SUMPRODUCT((F4:F400=E20)*(F4:F400<E21))

On Jun 21, 4:59 am, "Vacuum Sealed" wrote:
What would be the best approach for the following scenario:
[....]
Sub TrafficFlow()
Dim myRng As Range
Set myRng = [F4:F300]
If myRng.Value = #12:00:00 AM# And _
myRng.Value < #1:00:00 AM# _
Then Range("M5") = Count(myRng)
End If
If myRng.Value = #1:00:00 AM# And _
myRng.Value < #2:00:00 AM# _
Then Range("M6") = Count(myRng)
End If

Sub TrafficFlow()
Dim myRng As Range, c as Range
Set myRng = Range("F4:F300")
For Each c in myRng
Range("M" & 5+Hour(c)) = myRng.Count
Next

In a later posting, you used cells(6,i).Count. That makes little
sense since the Count is always 1 in that context.

Thx Joe

Your're right about your assumption of not making sense...

Much of what I submit here makes little sense to me as everyday is a
learning day for me here, although some things take longer to mash into the
gray matter.

will this also then move down the target range of M as each time frame count
value is recorded in a different cell in "M"

eg

upto 1.00am.Value.Count = "M5"
upto 2.00am.Value.Count = "M6"

and so forth to "M28"

Thx again
Mick.

On Jun 21, 7:26*am, "Vacuum Sealed" wrote:
Your're right about your assumption of not making sense...
Much of what I submit here makes little sense to me as
everyday is a learning day for me here

No need to get defensive. My comment was not meant as criticism. I
was trying to point the meaning of the Count property.

And on second thought, I wonder if you do not mean to use Count at
all. It returns the number of cells in a range. So myRng.Count is
297 with your range of F4:F300.

I wonder if you want is:

For each c in myRng
Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1
Next

Sorry Joe

I tried your code and it returned a value of 297 in each of the target
cells, which isn't quite right as only aroun 50 cells are populated for
testing.

Appreciated the assist.
Mick.

Also Don

I tried it this way.

For i = 4 To 300

With Application
.ScreenUpdating = False
End With

Select Case Cells(i, 6).SpecialCells(xlCellTypeConstants).Count

Case #12:00:01 AM# To #1:00:00 AM#
Range("M5") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #1:00:01 AM# To #2:00:00 AM#
Range("M6") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #2:00:01 AM# To #3:00:00 AM#
Range("M7") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #3:00:01 AM# To #4:00:00 AM#
Range("M8") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #4:00:01 AM# To #5:00:00 AM#
Range("M9") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #5:00:01 AM# To #6:00:00 AM#
Range("M10") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #6:00:01 AM# To #7:00:00 AM#
Range("M11") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count

End Select

No luck either

Thx again
Mick.