Notes

Sub delete_class()

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim nextWS As Worksheet
Dim originalWS As Worksheet
Dim start_row As Integer
Dim start_column As Integer
Dim pointer_row As Integer
Dim pointer_column As Integer
Dim CID As Variant
Dim i As Integer
Dim no_week As Integer
Dim seat_per_class As Integer
Dim spreadsheet_height As Integer
Dim empty_seat As Integer
Dim temp As Variant
Dim attending_student As Integer



spreadsheet_height = 1152
seat_per_class = 8
'8 seats per class

start_row = 0
no_week = 0
start_column = 7 'this is the column of the CID
attending_student = 0

CID = InputBox("Please input the CID Number that you want to delete classes for, starting from this week")

For i = 1 To spreadsheet_height

If Cells(i, start_column) = "CID" & CID Then
start_row = i
Exit For
End If
Next i
'find out which row is the inputed CID

If start_row = 0 Then
MsgBox "Cannot find the input CID"
Exit Sub
End If

If MsgBox("Are you sure you want to delete classes for " & Cells(i, 9).Value & " ?", vbYesNo, "Create Next Week") = vbNo Then Exit Sub


Set nextWS = ActiveSheet
Set originalWS = ActiveSheet

nextWS.Activate

'now start looping through weeks, deleting seats
Do Until nextWS Is Nothing



start_row = 0

For i = 1 To spreadsheet_height

If Cells(i, start_column) = "CID" & CID Then
start_row = i
Exit For
End If
Next i
'find out which row is the inputed CID

If start_row <> 0 Then
'remove the line
Range(Cells(i, 7), Cells(i, 13)).ClearContents
Cells(i, 20).ClearContents
End If


no_week = no_week + 1

On Error GoTo ErrorHandler2
Set nextWS = ActiveWorkbook.Worksheets(Cells(3, 20).Value)
nextWS.Activate

Loop
'looping through each week







ErrorHandler2:

Resume Finish_add

Finish_add:

MsgBox no_week & " classes have been deleted for CID" & CID

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

originalWS.Activate
End Sub