Making an auxiliary table with adding new data

[harber95]

14147

The table in sheet2 counts the number of students that received a certain grade.
I don't know how to chance the values in sheet2 according to adding new students' grades that appear in sheet1.

In other words, the data added in the user form "B_insert_grade" to sheet1 must correspond to sheet2.

Thanks for the help!

[Paul_Hossler]

1. The two subs that you have in Sheet2 code module would normally be in a Standard module as I did in the attachment with post #9


http://www.vbaexpress.com/forum/showthread.php?53418-Making-an-Auxiliary-Table-For-Grade-Distibution


2. I also fixed the name of the 'ThisWorkbook' in that attachment


3. Use the additional macro from that thread post #17 and add it to SumGrades to generate the totals


4. The use of .CurrentRegion (look in online help) will capture the current block of data around A1, including any adds or deletes so call SumGrades after you add or delete students or change grades




Set rStudents = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
Set rGrades = Worksheets("Sheet2").Cells(1, 1).CurrentRegion

[harber95]

Where do I place the code presented to me in post #9? I placed it in the "Make chart" button in the last userform C_grade_distribution.

What does "I also fixed the name of the 'ThisWorkbook' in that attachment" mean?

Where do I place the code in section 4.?

14153
Also, I did a few tweaks with the project, and there is something that bothers me: in sheet 2, the row that contains the lowest grade (0-35), will change its value to the name of the subject after pressing the "Make chart" button. I think this will make an incorrect chart.

[Paul_Hossler]

1. I think you have the grade summing code correct in the 'SumGrades' macro and in a Standard Module. When I run that macro, the summary on sheet is correct. I don't see any of that in CommandButton1_Click on C_grade_distribution?



Sub SumGrades()
Dim rStudents As Range, rGrades As Range
Dim iStudent As Long, iGrade As Long


Set rStudents = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
Set rGrades = Worksheets("Sheet2").Cells(1, 1).CurrentRegion

With rGrades
.Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1).Clear
End With

With rStudents
For iStudent = 2 To .Rows.Count
For iGrade = 7 To 10
Select Case .Cells(iStudent, iGrade).Value
Case 86 To 100
rGrades.Cells(2, iGrade - 5).Value = rGrades.Cells(2, iGrade - 5).Value + 1
Case 66 To 85
rGrades.Cells(3, iGrade - 5).Value = rGrades.Cells(3, iGrade - 5).Value + 1
Case 51 To 65
rGrades.Cells(4, iGrade - 5).Value = rGrades.Cells(4, iGrade - 5).Value + 1
Case 36 To 50
rGrades.Cells(5, iGrade - 5).Value = rGrades.Cells(5, iGrade - 5).Value + 1
Case Else
rGrades.Cells(6, iGrade - 5).Value = rGrades.Cells(6, iGrade - 5).Value + 1
End Select
Next iGrade
Next iStudent
End With

With rGrades
For iGrade = 2 To .Rows.Count
.Cells(iGrade, 6).Value = .Cells(iGrade, 2).Value + _
.Cells(iGrade, 3).Value + _
.Cells(iGrade, 4).Value + _
.Cells(iGrade, 5).Value
Next iGrade
End With

End Sub


2. The 'ThisWorkbook' name was some garbage characters (see picture)


3. In


Private Sub CommandButton1_Click()

.....

Range("B" & lngMyRow).Value = ComboBox1

'''''




Range("B:F") is on what ever worksheet happens to be active. Try changing all such things to Worksheets("Sheet2").Range("B:F")




14160