bubbapost
03-26-2010, 11:38 AM
Hello,
I need help combining the 5 macro's below into one.
Any Suggestions would be appreciated :hi:
Option Explicit
Const StartRow = 2
Const EndRow = 3
Const StartCol = 3
Const Brk1Start = 4
Const Brk1End = 5
Const LunchStart = 6
Const LunchEnd = 7
Const Brk2Start = 8
Const Brk2End = 9
Const StopCol = 10
Const DivCol = 13
Const M1Col = 14
Const M2Col = 15
Const M3Col = 16
Const M4Col = 17
Const M5Col = 18
Sub ScheduleTrainingCS1()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1End) < Cells(StartRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
Cells(i, M1Col).Value = "1"
If Counter.Value = CSCap.Value Then Exit Sub
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCS2()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1Start) >= Cells(EndRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
Cells(i, M1Col).Value = "1"
If Counter.Value = CSCap.Value Then Exit Sub
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA1()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1Start) >= Cells(EndRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) <> "1" Then
Cells(i, M1Col).Value = "A"
End If
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA2()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1End) < Cells(StartRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) <> "1" Then
Cells(i, M1Col).Value = "A"
End If
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA3()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) = "" Then
Cells(i, M1Col).Value = "A"
End If
End If
Next i
End Sub
Thank you!
I need help combining the 5 macro's below into one.
Any Suggestions would be appreciated :hi:
Option Explicit
Const StartRow = 2
Const EndRow = 3
Const StartCol = 3
Const Brk1Start = 4
Const Brk1End = 5
Const LunchStart = 6
Const LunchEnd = 7
Const Brk2Start = 8
Const Brk2End = 9
Const StopCol = 10
Const DivCol = 13
Const M1Col = 14
Const M2Col = 15
Const M3Col = 16
Const M4Col = 17
Const M5Col = 18
Sub ScheduleTrainingCS1()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1End) < Cells(StartRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
Cells(i, M1Col).Value = "1"
If Counter.Value = CSCap.Value Then Exit Sub
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCS2()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1Start) >= Cells(EndRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
Cells(i, M1Col).Value = "1"
If Counter.Value = CSCap.Value Then Exit Sub
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA1()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1Start) >= Cells(EndRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) <> "1" Then
Cells(i, M1Col).Value = "A"
End If
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA2()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, StartCol) <= Cells(StartRow, M1Col) Then
If Cells(i, Brk1End) < Cells(StartRow, M1Col) Then
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) <> "1" Then
Cells(i, M1Col).Value = "A"
End If
End If
End If
End If
Next i
End Sub
Sub ScheduleTrainingCSA3()
Dim Counter As Range
Set WBT = ActiveWorkbook
Set WSD = WBT.Worksheets("Dashboard")
Set WSC = WBT.Worksheets("Combined")
Set WSTI = WBT.Worksheets("Training Info")
Set CSCap = WSTI.Range("CSAgent_Cap")
Set Counter = WSD.Range("E3")
FinalRow = WSC.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
If Cells(i, DivCol) = "CS" Then
If Cells(i, M1Col) = "" Then
Cells(i, M1Col).Value = "A"
End If
End If
Next i
End Sub
Thank you!