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
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