PDA

View Full Version : Solved: Select Date Range



Nosstech
08-06-2008, 07:33 AM
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
08-06-2008, 08:19 AM
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
08-06-2008, 09:43 AM
Thanks. I will give that a try.

Nosstech
08-06-2008, 10:37 AM
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
08-06-2008, 11:01 AM
You don't, it is already visible in the formula cell. If you have code, you could just read that cell's value.

Nosstech
08-06-2008, 11:04 AM
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
08-06-2008, 11:05 AM
Yes as I said. Assuming the formula is in C1



MsgBox Range("C1").Value

Nosstech
08-06-2008, 11:20 AM
Yes as I said. Assuming the formula is in C1



MsgBox Range("C1").Value


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

Nosstech
08-07-2008, 06:41 AM
Yes as I said. Assuming the formula is in C1



MsgBox Range("C1").Value


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
08-07-2008, 06:55 AM
Is this what you want?


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

Nosstech
08-07-2008, 07:02 AM
Is this what you want?


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


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
08-09-2008, 03:19 PM
Assuming that you can get your calculations correct in cells D1, D2,& D3. You could get the msgBox to display the values by

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

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