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))

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.

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.

Don

Tried your suggestion

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

No good.

Thx agian.
Mick.

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.

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.

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, 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

Thx again Don.

Your code modded.

=SUMPRODUCT(($F$4:$F$300=$O5)*($F$4:$F$3400<$P5))

eg Column "O" = 00:01 & Column "P" = 01:00

This returned N/A#

Regards
Mick.

Sorry Joe

That halted on:

Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1

Regards
Mick.

On Jun 21, 7:48*am, "Vacuum Sealed" wrote:
Sorry Joe
That halted on:
* * Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1

Works fine for me. I don't know how much spoon-feeding you need.
Copy and paste the following:

Sub TrafficFlow()
Dim myRng As Range, c As Range
Set myRng = Range("F4:F300")
For Each c In myRng
Range("M" & 5 + Hour(c)) = Range("M" & 5 + Hour(c)) + 1
Next
End Sub

Now, you never explained what you want the code to do. Something
like: "F4:F300 contains the times of individual observations. In
M5:M28, I want to count the number of observations in each hour of the
day".

That is what I assume by the code above. There are better was to
implement it. I tried to stray as little from your original
implementation as possible initially.

If you want to avoid VBA altogether, put the following formula into M5
and copy down through M28:

=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

Be sure to copy-and-paste the formula.

On Jun 21, 10:24*am, joeu2004 wrote:
On Jun 21, 7:48*am, "Vacuum Sealed" wrote:

Sorry Joe
That halted on:
* * Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1

Works fine for me. *I don't know how much spoon-feeding you need.
Copy and paste the following:

Sub TrafficFlow()
Dim myRng As Range, c As Range
Set myRng = Range("F4:F300")
For Each c In myRng
* *Range("M" & 5 + Hour(c)) = Range("M" & 5 + Hour(c)) + 1
Next
End Sub

Now, you never explained what you want the code to do. *Something
like: *"F4:F300 contains the times of individual observations. *In
M5:M28, I want to count the number of observations in each hour of the
day".

That is what I assume by the code above. *There are better was to
implement it. *I tried to stray as little from your original
implementation as possible initially.

If you want to avoid VBA altogether, put the following formula into M5
and copy down through M28:

=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

Be sure to copy-and-paste the formula.

Errata...

On Jun 21, 8:24*am, joeu2004 wrote:
If you want to avoid VBA altogether, put the following
formula into M5 and copy down through M28:
=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

Since you said that F4:F300 might not be fully populated, the
SUMPRODUCT formula should be:

=SUMPRODUCT(($F$4:$F$300<"")*(HOUR($F$4:$F$300)=R OWS($M$5:M5)-1))

I wrote regarding a VBA implementation:
There are better [ways] to implement it.

Here is one:

Sub countTimes()
'*** modify Consts as needed
Const srcRng As String = "f4" 'should be 1 cell
Const destRng As String = "m5:m28" 'should be 24 cells
'*** hr is two-dimensional to make it
'*** easier to write into Range(destRng)
Dim hr(0 To 23, 1 To 1) As Long, i As Long, x As Long
Dim data As Variant
'*** assume contiguous data starting in srcRng
If Not IsEmpty(Range(srcRng)) Then
'*** create data(1 to n, 1 to 1) as Variant
data = Range(srcRng, Range(srcRng).End(xlDown))
For i = 1 To UBound(data, 1)
x = Hour(data(i, 1))
hr(x, 1) = hr(x, 1) + 1
Next
End If
Range(destRng) = hr
End Sub

PS....

On Jun 21, 8:24*am, joeu2004 wrote:
you never explained what you want the code to do. *Something
like: *"F4:F300 contains the times of individual observations.
*In M5:M28, I want to count the number of observations in each
hour of the day".

I was thrown off by your use of the Count property. I neglected to
put everything in the context of your subject line. Always best to
"duplicate" (really expand on) the subject in the body of your
posting.

For future note, one way to learn what a particular property or method
does is: if you write in a proper context as you did in some of your
posting, e.g. cells(i,6).count, you can put the mouse cursor over the
word "count", then press F1 to see the help page.

I think then you might have seen that it was not the type of counting
that you needed.

Arguably, VBA help pages are not always so helpful; typical of
Microsoft documentation, IMHO. But I wanted to explain how to use F1
for the future.

You are so right Joe

I have much to learn, and continue to do so with the valuable assistance I
get here from generous people like yourself who give freely of their
knowledge and experience...

I will try and put more emphasis on what it is I need along with expected
outcome, without dragging it out into an epic....

BTW:

Your nested formula worked nicely.

The VB one did not as it returned a value of just "3" in two of the cells.

I will settle for the nested and forego the VB as I have to move onto
another project.

Once again

A huge thx to both Don & yourself for your patience with me.

Mick

And again

Sorry Joe

I neglected to increase the range in srcRng to "F4:F300", interestingly the
first cell "M5" had a count value of 177, yet the balance of the cells below
all displayed the correct count.

All the same, I will stick with the nested formula.

Cheers
Mick.