PDA

View Full Version : Selecting a Range of Dates with a Specific Month



giraffe3289
04-01-2009, 12:13 PM
So I have numerous sheets, one for each employee. And each employee goes to their respective sheet and enters the date, how many scheduled appointments they have, how many of those are cancelled, and how many are no shows. I want to calculate the cancellation/no show rate for EVERYONE for one particular month.

So the code should go to each sheet, figure out which rows are the appropriate month, and add up the number of scheduled appointments, the number of cancellations, and the number of no-shows. It should then go to the next sheet and do the same, until finally we have a total for scheduled, cancellations, and no-shows for the whole company. It should then use those values to calculate the cancellation/no show rate.

This is the code I have so far. Its only set up for one cell on one sheet. I'm not really sure how to apply it to all of the sheets and to get out the correct set of rows based on the month. Please help!




Option Explicit

Sub Monthly_CaNS(MonthName)

Dim MonthNum As Integer
Dim TheDate As Date
Dim Scheduled As Integer
Dim Cancellation As Integer
Dim NoShow As Integer
Dim CaNS_Sum As Integer
Dim CaNS_Rate As Single
Dim CaNS_Percent As Single
If MonthName = "January" Then
MonthNum = 1
ElseIf MonthName = "February" Then
MonthNum = 2
ElseIf MonthName = "March" Then
MonthNum = 3
ElseIf MonthName = "April" Then
MonthNum = 4
ElseIf MonthName = "May" Then
MonthNum = 5
ElseIf MonthName = "June" Then
MonthNum = 6
ElseIf MonthName = "July" Then
MonthNum = 7
ElseIf MonthName = "August" Then
MonthNum = 8
ElseIf MonthName = "September" Then
MonthNum = 9
ElseIf MonthName = "October" Then
MonthNum = 10
ElseIf MonthName = "November" Then
MonthNum = 11
Else
MonthNum = 12
End If

TheDate = Range("A2").Value

If DatePart("m", TheDate) = MonthNum Then
Scheduled = Range("C2").Value
Cancellation = Range("D2").Value
NoShow = Range("E2").Value
CaNS_Sum = Application.WorksheetFunction.Sum(Cancellation, NoShow)
CaNS_Rate = CaNS_Sum / Scheduled
CaNS_Percent = CaNS_Rate * 100
MsgBox ("The cancellation/no show rate is " & CaNS_Percent & "%.")
Else
Exit Sub
End If
End Sub

Sub CallMonthly_CaNS()
Call Monthly_CaNS(InputBox("Please enter the month.", "Monthly Cancellation/No Show"))
End Sub

georgiboy
04-01-2009, 01:19 PM
Maybe something like this (untested)...

Option Explicit

Sub Monthly_CaNS(MonthName)
Dim ws As Worksheet
Dim rCell As Range

Dim MonthNum As Integer
Dim TheDate As Date
Dim Scheduled As Integer
Dim Cancellation As Integer
Dim NoShow As Integer
Dim CaNS_Sum As Integer
Dim CaNS_Rate As Single
Dim CaNS_Percent As Single
If MonthName = "January" Then
MonthNum = 1
ElseIf MonthName = "February" Then
MonthNum = 2
ElseIf MonthName = "March" Then
MonthNum = 3
ElseIf MonthName = "April" Then
MonthNum = 4
ElseIf MonthName = "May" Then
MonthNum = 5
ElseIf MonthName = "June" Then
MonthNum = 6
ElseIf MonthName = "July" Then
MonthNum = 7
ElseIf MonthName = "August" Then
MonthNum = 8
ElseIf MonthName = "September" Then
MonthNum = 9
ElseIf MonthName = "October" Then
MonthNum = 10
ElseIf MonthName = "November" Then
MonthNum = 11
Else
MonthNum = 12
End If

For Each ws In ThisWorkbook.Worksheets
For Each rCell In ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)
TheDate = rCell.Value

If DatePart("m", TheDate) = MonthNum Then
Scheduled = Scheduled + rCell.Offset(, 2).Value
Cancellation = Cancellation + rCell.Offset(, 3).Value
NoShow = NoShow + rCell.Offset(, 4).Value
Else
Exit Sub
End If
Next rCell
Next ws

CaNS_Sum = Application.WorksheetFunction.Sum(Cancellation, NoShow)
CaNS_Rate = CaNS_Sum / Scheduled
CaNS_Percent = CaNS_Rate * 100
MsgBox ("The cancellation/no show rate is " & CaNS_Percent & "%.")

End Sub

Sub CallMonthly_CaNS()
Call Monthly_CaNS(InputBox("Please enter the month.", "Monthly Cancellation/No Show"))
End Sub
If you provide example data in the form of a workbook we can maybe look a bit deeper.

Hope this helps