Selecting a Range of Dates with a Specific Month

[giraffe3289]

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]

Maybe something like this (untested)...

[vba]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[/vba]
If you provide example data in the form of a workbook we can maybe look a bit deeper.

Hope this helps