CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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)) |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
Don
Tried your suggestion =SUMPRODUCT(($F$4:$F$300#12:00:01#)*($F$4:$F$300, <#1:00:01#)) No good. Thx agian. Mick. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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)) |
CountIf / SumProduct in VB
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 |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
Sorry Joe
That halted on: Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1 Regards Mick. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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 |
CountIf / SumProduct in VB
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. |
CountIf / SumProduct in VB
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 |
CountIf / SumProduct in VB
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. |
All times are GMT +1. The time now is 04:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com