Sleeper: Help to select date range w/ count

[RompStar]

Ok, Imagine that I have 2 ROWS..

B and C

B contains Dates
C Contains the WORD: Yes or No

Starting at A9 and below where the data starts, A8 and above are headers.

I want to search say 3 weeks, FROM 7/1/05 TO 7/18/05 from column B, please keep in mind that I need it to work even if the dates are not sorted right, I need it to pick only what I selected from the entire range from column B.

So it would select my Range, maybe using autofilter, then it would count that range that I want, so for each Date in a single cell, that would count as 1, so 10 dates would be count 10. Then I need to store that into a variable, which I think I know how to do, just don't know how to select my range and count it.

Then as my date range is selected, right next to B is C, then I would want it to count the instances of the Word Yes, count it in the same range that the dates were picked from, but from column C, store that value.

and then do simple math and divide count of Yes into the count of the number of dates to get my percentage.

I did some script before with help, so I pretty much know how to do everything, except how to select my dates, count them, count the yes to the right of it..

That's what I need help with, can someone please help me, thanks.

:mkay

[RompStar]

maybe with something like this to select the range ?


Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(BeginDate), _
Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

[RompStar]

just started to write the VBA code, I think I have everything, just not sure how to count the Range and the Yes in the next column for the Range, can anyone help ?


Sub SearchDates()
' Dim LastRow As Long
Dim CountRng As Integer
Dim CountYes As Integer
Dim Rng As Range
Dim Msg As Integer
Dim BeginDate As Date
Dim EndDate As Date
' Dim c As Range
' Dim Item As Range
' LastRow = Range("J10").End(xlDown).Row
Set Rng = Range("B9:B" & Range("B65536").End(xlUp).Row)
' Turn OFF Screen Updating... to speed things up...
Application.ScreenUpdating = False
' Ask for the beginning date and check to make sure the input is a date format
Do
Msg = vbOK
BeginDate = Application.InputBox("Enter Starting Date from column J:", _
"Range Beginning", Type:=1)
If Not IsDate(BeginDate) Then
Msg = MsgBox("Entry not a valid date!", vbCritical + vbRetryCancel, _
"Error: Invalid Date")
End If
BeginDate = DateValue(BeginDate)
' Ask for the end date and check to make sure the input is a date format
Loop While Msg = vbRetry
Do
Msg = vbOK
EndDate = Application.InputBox("Enter Ending Date from column J:", _
"Range Ending", Type:=1)
If Not IsDate(EndDate) Then
Msg = MsgBox("Entry not a valid date!", vbCritical + vbRetryCancel, _
"Error: Invalid Date")
End If
EndDate = DateValue(EndDate)
Loop While Msg = vbRetry
' Display to the User the Range that they selected
MsgBox "You selected: " & BeginDate & " through " & EndDate & " ", , _
"Select Range"
' Do something with the date values
On Error GoTo Finish
Range("A8:F8").Select
Selection.AutoFilter
Rng.AutoFilter Field:=2, Criteria1:=">=" & CLng(BeginDate), _
Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
------------- this section, not sure...
' 1. Now count the Range and store the value into CountRng
' ???? Need Help!!! please.
' CountRng = Rng.Count, need help, not even sure if I am thinking it right.
' 2. Next count the instances of Yes in the Date Range from Column C
CountYes = Rng.Offset(0, 1).Count(Rng.Rows.Count "Yes")
'--- end section not sure and not working.
' -- write in values into the sheet
' write the value of the Range Count into cell I92 on the sheet
Range("B6").Value = CountRng
' Then write the value of the count into cell I93 on the sheet
Range("C6").Value = CountYes
' Stamp the BeginDate into cell H5 and EndDate into cell I5
Range("H5").Value = BeginDate
Range("I5").Value = EndDate
' turn off autofilter, make everything like it was before run of script
Range("A8:F8").Select
Selection.AutoFilter = False
' The rest of the calculations will be done on the sheet with formulas.
Finish:
End Sub

[Bob Phillips]

[QUOTE=RompStar]I did some script before with help, so I pretty much know how to do everything, except how to select my dates, count them, count the yes to the right of it../QUOTE]

Everything except the code eh? :)



Sub Macro1()
Dim iLastRow As Long
Dim sFormula As String
Dim dteStart As Date
Dim dteEnd As Date
Dim sDateFormat
Dim cMatches As Long
dteStart = InputBox("Supply start date")
If dteStart = 0 Then
Exit Sub
Else
dteEnd = InputBox("Supply end date")
If dteEnd = 0 Then
Exit Sub
End If
End If
sDateFormat = Range("B9").NumberFormat
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("B8:B" & iLastRow)
.AutoFilter Field:=1, _
Criteria1:=">=" & Format(CDate(dteStart), sDateFormat), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(CDate(dteEnd), sDateFormat)
cMatches = .SpecialCells(xlCellTypeVisible).Count - 1
sFormula = "SUMPRODUCT((C9:C" & iLastRow & "=""Yes"")*" & _
"(SUBTOTAL(3,OFFSET(B9:B" & iLastRow & ",ROW(B9:B" & iLastRow & _
")-MIN(ROW(B9:B" & iLastRow & ")),,1))))"
MsgBox cMatches & ", " & Format(Evaluate(sFormula) / cMatches, "0.0%") & "as Yes"
.AutoFilter
End With
End Sub

[RompStar]

:hi: wow, that's a new way to use the Range Object for me anyways :- )


I used this to output the value of:

Range("B6").Value = cMatches

into B6, so I have it after the VB ends it's run, and I wanted to do the
same with the %

but it's not working, it's writing in the wrong percentage, not what i see in the VB window..

Am I doing this right ?


Sub Macro1()
Dim iLastRow As Long
Dim sFormula As String
Dim dteStart As Date
Dim dteEnd As Date
Dim sDateFormat
Dim cMatches As Long
dteStart = InputBox("Supply start date")
If dteStart = 0 Then
Exit Sub
Else
dteEnd = InputBox("Supply end date")
If dteEnd = 0 Then
Exit Sub
End If
End If
sDateFormat = Range("B9").NumberFormat
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("B8:B" & iLastRow)
.AutoFilter Field:=1, _
Criteria1:=">=" & Format(CDate(dteStart), sDateFormat), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(CDate(dteEnd), sDateFormat)
cMatches = .SpecialCells(xlCellTypeVisible).Count - 1
sFormula = "SUMPRODUCT((C9:C" & iLastRow & "=""Yes"")*" & _
"(SUBTOTAL(3,OFFSET(B9:B" & iLastRow & ",ROW(B9:B" & iLastRow & _
")-MIN(ROW(B9:B" & iLastRow & ")),,1))))"
MsgBox cMatches & ", " & Format(Evaluate(sFormula) / cMatches, "0.0%") & "as Yes"
.AutoFilter
End With
Range("H3").Value = Evaluate(sFormula) / cMatches
Range("B6").Value = cMatches
End Sub


As you can see I am not an expert, only been working with VBA for 3 months and I never really programmed anything, and HTML doesn't count I think

:rotlaugh:
Like when I run 7/12/05 to 7/13/05 it the MsgBox says 97.1%, but when I try to write the value to a cell it writes 122.12

any idea what i am doing wrong ?

[RompStar]

when I put that .value inside the With End, it worked :- )

outside of with end it gave me the wrong number, just curious for learning
purposes, why ?

[mdmackillop]

Hi RompStar,
If you select your code and click on the VBA button, it formats as above, making it more readable. Also, please split long lines of code or it runs off the screen!
Regards
MD

[Bob Phillips]

when I put that .value inside the With End, it worked :- )

outside of with end it gave me the wrong number, just curious for learning
purposes, why ?

Not obvious to me mate, can you post a workbook with data and code?

[Bob Phillips]

I see what the problem is.

It is not the End With that is critical it is the .Autofilter. My SUMPRODUCT formula works on visible cells, so if you issue it after the .Autofilter, more cells are made visible, to the calculation is different.

[RompStar]

I see, that's good to know, more about autofilter and the range object the better :- )

thanks for the help:thumb