Couple of suggestions to consider
1. Create 3 module level variables and Set them at the beginning
Option Explicit
Dim wsSettings As Worksheet, wsSchedule As Worksheet, wsInterval As Worksheet
......
Public Sub generate_interval_report()
........
Set wsSettings = Worksheets("Settings")
Set wsSchedule = Worksheets("Schedule")
Set wsInterval = Worksheets("Interval")
2. Use With / End With to make it easier to see (same reason I personally don't like multiple statements on one line with a ":" )
With wsSettings
employee_start_position = .Range("employee_start_position")
..........
Set separator_code_range = .Range("separator_code_range")
End With
3. I noticed that there was a lot .Select-ing and re-Selecting in the main loop. You usually don't have to Select a WS or Cell to act on or with it. Look at my -------------------- markers
With wsSchedule ' no .Select --------------------
For employee = employee_start_position To employee_end_position
For day = 0 To 6
'-------------------- dot Cells to go with wsSchedule
schedule_code_value = .Cells(employee, schedule_code_start_position + (day * schedule_day_gap))
If Application.WorksheetFunction.CountIf(schedule_code_range, schedule_code_value) > 0 Then
schedule_code_group_value = schedule_code_range.Find(What:=schedule_code_value, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, 1)
If schedule_code_group_value = "Online" Then
employee_separator_value = .Cells(employee, employee_separator_position).Text
If WorksheetFunction.CountIf(separator_code_range, employee_separator_value) > 0 Then
interval_separator_unique_position = separator_code_range.Find(What:=employee_separator_value, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, 2)
'pull time values
'-------------------------------Worksheets("Schedule").Select
schedule_start_value = FormatDateTime(.Cells(employee, schedule_start_start_position + (day * schedule_day_gap)), 4)
schedule_break1_value = FormatDateTime(.Cells(employee, schedule_break1_start_position + (day * schedule_day_gap)), 4)
schedule_lunch_start_value = FormatDateTime(.Cells(employee, schedule_lunch_start_start_position + (day * schedule_day_gap)), 4)
schedule_lunch_end_value = FormatDateTime(.Cells(employee, schedule_lunch_end_start_position + (day * schedule_day_gap)), 4)
schedule_break2_value = FormatDateTime(.Cells(employee, schedule_break2_start_position + (day * schedule_day_gap)), 4)
schedule_end_value = FormatDateTime(.Cells(employee, schedule_end_start_position + (day * schedule_day_gap)), 4)
If IsEmpty(schedule_lunch_end_value) Then
schedule_lunch_end_value = FormatDateTime(DateAdd("n", 30, schedule_lunch_start_value), 4)
End If
'shift
'---------------------Worksheets("Interval").Select
interval_start_index = getRowByString(schedule_start_value)
interval_end_index = getRowByString(schedule_end_value)
'fix midnight position; derp
If interval_start_index = 100 Then
interval_start_index = 4
ElseIf interval_end_index = 4 Then
interval_end_index = 100
End If
For interval = (interval_start_index + interval_separator_unique_position - interval_separator_combined_position) To ((interval_end_index - 1) + interval_separator_unique_position - interval_separator_combined_position)
'---------------------------
wsInterval.Cells(interval, interval_shift_start_position + 1 + (day * interval_day_gap)).Value = wsInterval.Cells(interval, interval_shift_start_position + 1 + (day * interval_day_gap)).Value + 1
Next interval
'break1
If Not schedule_break1_value = "00:00" Then
interval_start_index = getRowByString(schedule_break1_value) + interval_separator_unique_position - interval_separator_combined_position
wsInterval.Cells(interval_start_index, interval_break_start_position + 1 + (day * interval_day_gap)).Value = wsInterval.Cells(interval_start_index, interval_break_start_position + 1 + (day * interval_day_gap)).Value + 1
End If
'break2
If Not schedule_break2_value = "00:00" Then
interval_start_index = getRowByString(schedule_break2_value) + interval_separator_unique_position - interval_separator_combined_position
wsInterval.Cells(interval_start_index, interval_break_start_position + 1 + (day * interval_day_gap)).Value = wsInterval.Cells(interval_start_index, interval_break_start_position + 1 + (day * interval_day_gap)).Value + 1
End If
'lunch
If Not schedule_lunch_start_value = "00:00" Or Not schedule_lunch_end_value = "00:00" Then
interval_start_index = getRowByString(schedule_lunch_start_value)
interval_end_index = getRowByString(schedule_lunch_end_value)
For interval = (interval_start_index + interval_separator_unique_position - interval_separator_combined_position) To ((interval_end_index - 1) + interval_separator_unique_position - interval_separator_combined_position)
wsInterval.Cells(interval, interval_lunch_start_position + 1 + (day * interval_day_gap)).Value = wsInterval.Cells(interval, interval_lunch_start_position + 1 + (day * interval_day_gap)).Value + 1
Next interval
End If
interval_separator_unique_position = vbNullString
End If
End If
End If
' ---------------------------------------Worksheets("Schedule").Select
Next day
Next employee
End With '------------------------------------------------
With Application
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True: .DisplayStatusBar = True: .EnableEvents = True
End With
Worksheets("Schedule").Select ' -------------------------------- Activate
Exit Sub
Errorcatcher:
MsgBox ("Something went wrong;" & vbNewLine & vbNewLine & Err.Description)
Resume Next
End Sub
Function getRowByString(ByVal thisHour As String)
Dim row_number As String
'---------------------------- Worksheets("Interval").Select
'----------------------------- wsInterval.Range("interval_range").Select
wsInterval.Range("interval_range").Find(What:=thisHour, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
row_number = ActiveCell.Row
getRowByString = row_number
End Function