Solved: Select Date Range

[Nosstech]

Hello,

How do I have a user select a date range and have a msg box display number of hits?

1. User Selects Date Range (combo box or txt box, doesn't matter)
2. Script searches for date range within Excel (Col A)
3. Msg Box Displays number of hits from user's input.

Thanks in advance for the help.

[Bob Phillips]

The worst part of this is coding the selection of a date range. Why not just have the start and end dates in 2 cells and use

=SUMPRODUCT(--(A2:A200>=B1),--(A2:A200<=B2))

[Nosstech]

Thanks. I will give that a try.

[Nosstech]

The worst part of this is coding the selection of a date range. Why not just have the start and end dates in 2 cells and use

=SUMPRODUCT(--(A2:A200>=B1),--(A2:A200<=B2))

How do I display my total in a message box?

[Bob Phillips]

You don't, it is already visible in the formula cell. If you have code, you could just read that cell's value.

[Nosstech]

You don't, it is already visible in the formula cell. If you have code, you could just read that cell's value.

OK. Is there a way to show the results in a msgbox? That is what my boss wants.

[Bob Phillips]

Yes as I said. Assuming the formula is in C1

[vba]

MsgBox Range("C1").Value
[/vba]

[Nosstech]

Yes as I said. Assuming the formula is in C1

[vba]

MsgBox Range("C1").Value
[/vba]

Thanks, it worked!!! I swear I tried that before. Anyway, thanks for the help.

[Nosstech]

Yes as I said. Assuming the formula is in C1

[vba]

MsgBox Range("C1").Value
[/vba]

I have one more question...

We have different categories within our spreadsheet. Is it possible to group each category within the selected sumproduct?

Example:

User searches for a date range of 7/1/08 and 7/31/08
Msgbox Output
5 Violations:
3 Not Closed
2 Premature

Thanks again.

[Bob Phillips]

Is this what you want?

[vba]
Select Case Range("C1").Value
Case 5: Msgbox "Violations"
Case 3: MsgBox "Not Closed"
Case 2: MsgBox "Premature"
End Select
[/vba]

[Nosstech]

Is this what you want?

[vba]
Select Case Range("C1").Value
Case 5: Msgbox "Violations"
Case 3: MsgBox "Not Closed"
Case 2: MsgBox "Premature"
End Select
[/vba]

I want the script to count the individual violations based on the date range the user selects. This is what I had in mind in terms of functions (probably wrong);

Total Violations : 5 (MsgBox Range("F5").Value)
Not Closed: 3 (countif(c:c,"Not Closed") Within date range selected)
Premature: 2 (countif(c:c,"Premature") Within date range selected)

Is this possible?

[Aussiebear]

Assuming that you can get your calculations correct in cells D1, D2,& D3. You could get the msgBox to display the values by

[VBA]MsgBox "Total Violations :" & Range("D1").Value & vbCrLf & "Total Not Closed :" & Range("D2").Value _
& vbCrLf & "Total Premature :" & Range("D3").Value[/VBA]

Change the cell references to where ever you set your calculation results to be