Hi,
I am trying to create a weekly attendance calculation for a set of students with roll no in Column A in both sheets as reference. If a student have enrolled in a course it will be a new row entry in sheet 2, therefore for each student based on the number of courses they took they have multiple row entry with a start date and end date for each course.
Sheet 1 has unique roll numbers for each student and a column for each week for the year, I have to run a VBA to match Sheet 2 each roll number to sheet1 and then add +1 for each subject part of that week based on the start date and end date for the course.
Can someone help me make a simple VBA code for this. I tried to make something but it seems to be bugged.
LR1 = Worksheet("Course").Cells(Rows.Count, "A").End(xlUp).Row
CL1 = Worksheet("Attendance").Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To ActiveSheet.ListObjects("Attendance").ListRows.Count
For j = 4 To LR1
If Worksheet("Attendance").Cells(i, 1).Value = Worksheet("Course").Cells(j, 1) Then
Dim rng As Range
Set rng = Worksheet("Course").Range(Cell(i, 2), Cell(i, CL1))
rng.Formula = "if(AND(" Worksheet("Course").Cells(j, 8)>Worksheet("Attendance").B$1
Worksheet("Attendance").Cell(i,2).value = Worksheet("Attendance").Cell(i,2).value +1
End If
Next j
Next i
I am trying to create a weekly attendance calculation for a set of students with roll no in Column A in both sheets as reference. If a student have enrolled in a course it will be a new row entry in sheet 2, therefore for each student based on the number of courses they took they have multiple row entry with a start date and end date for each course.
Sheet 1 has unique roll numbers for each student and a column for each week for the year, I have to run a VBA to match Sheet 2 each roll number to sheet1 and then add +1 for each subject part of that week based on the start date and end date for the course.
Can someone help me make a simple VBA code for this. I tried to make something but it seems to be bugged.
LR1 = Worksheet("Course").Cells(Rows.Count, "A").End(xlUp).Row
CL1 = Worksheet("Attendance").Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To ActiveSheet.ListObjects("Attendance").ListRows.Count
For j = 4 To LR1
If Worksheet("Attendance").Cells(i, 1).Value = Worksheet("Course").Cells(j, 1) Then
Dim rng As Range
Set rng = Worksheet("Course").Range(Cell(i, 2), Cell(i, CL1))
rng.Formula = "if(AND(" Worksheet("Course").Cells(j, 8)>Worksheet("Attendance").B$1
Worksheet("Attendance").Cell(i,2).value = Worksheet("Attendance").Cell(i,2).value +1
End If
Next j
Next i
