Case problem?

[nadiminti]

Hi

I am using the following sub for calculating the Grade.

The problem I'm facing is that for the same value of 5 or 8 entered in each text box, I am getting different grades. B+, B & C

Can some one help please? The MsgBoxes were introduced to find out the values that are getting loaded. My suspicion is the Case where it is checking the value. Do not understand where I'm going wrong

Private Sub calcGrade()
Dim i As Integer
Dim j As Integer
Dim subGradenum As Integer
Dim subGradeArray(0 To 11) As String
    
For i = 0 To 11 'Subject
    j = i
 'TextBox12, 19, 26, 32, 39, 46, 53, 60, 66, 72, 78, 84 for Grade auto-calculation
Select Case j
    
    Case 0
    subGradenum = CInt(100 * ((CInt(TextBox7.Value) + CInt(TextBox8.Value) + CInt(TextBox9.Value) + CInt(TextBox10.Value) + CInt(TextBox11.Value)) / 80))

    Case 1
    subGradenum = CInt(100 * ((CInt(TextBox13.Value) + CInt(TextBox14.Value) + CInt(TextBox15.Value) + CInt(TextBox16.Value) + CInt(TextBox17.Value) + CInt(TextBox18.Value)) / 80))

    Case 2
    subGradenum = CInt(100 * ((CInt(TextBox20.Value) + CInt(TextBox21.Value) + CInt(TextBox22.Value) + CInt(TextBox23.Value) + CInt(TextBox24.Value) + CInt(TextBox25.Value)) / 80))

    Case 3
    subGradenum = CInt(100 * ((CInt(TextBox27.Value) + CInt(TextBox28.Value) + CInt(TextBox29.Value) + CInt(TextBox30.Value) + CInt(TextBox31.Value)) / 80))

    Case 4
    subGradenum = CInt(100 * ((CInt(TextBox33.Value) + CInt(TextBox34.Value) + CInt(TextBox35.Value) + CInt(TextBox36.Value) + CInt(TextBox37.Value) + CInt(TextBox38.Value)) / 80))

    Case 5
    If stuClass = 6 Or stuClass = 7 Then
    subGradenum = CInt(100 * ((CInt(TextBox40.Value) + CInt(TextBox41.Value) + CInt(TextBox42.Value) + CInt(TextBox43.Value) + CInt(TextBox44.Value) + CInt(TextBox45.Value)) / 80))
    End If

    Case 6
    If stuClass = 8 Or stuClass = 9 Or stuClass = 10 Then
    subGradenum = CInt(100 * ((CInt(TextBox47.Value) + CInt(TextBox48.Value) + CInt(TextBox49.Value) + CInt(TextBox50.Value) + CInt(TextBox51.Value) + CInt(TextBox52.Value)) / 80))
    End If
    
    Case 7
    If stuClass = 8 Or stuClass = 9 Or stuClass = 10 Then
    subGradenum = CInt(100 * ((CInt(TextBox54.Value) + CInt(TextBox55.Value) + CInt(TextBox56.Value) + CInt(TextBox57.Value) + CInt(TextBox58.Value) + CInt(TextBox59.Value)) / 80))
    End If

    Case 8
    subGradenum = CInt(100 * ((CInt(TextBox61.Value) + CInt(TextBox62.Value) + CInt(TextBox63.Value) + CInt(TextBox64.Value) + CInt(TextBox65.Value)) / 100))
    'MsgBox subGradenum & ", " & TextBox61.Value & ", " & TextBox62.Value & ", " & TextBox63.Value & ", " & TextBox64.Value & ", " & TextBox65.Value
    Case 9
    subGradenum = CInt(100 * ((CInt(TextBox67.Value) + CInt(TextBox68.Value) + CInt(TextBox69.Value) + CInt(TextBox70.Value) + CInt(TextBox71.Value)) / 100))
    'MsgBox subGradenum & ", " & TextBox67.Value & ", " & TextBox68.Value & ", " & TextBox69.Value & ", " & TextBox70.Value & ", " & TextBox71.Value
    Case 10
    subGradenum = CInt(100 * ((CInt(TextBox73.Value) + CInt(TextBox74.Value) + CInt(TextBox75.Value) + CInt(TextBox76.Value) + CInt(TextBox77.Value)) / 100))
    'MsgBox subGradenum
    Case 11
    subGradenum = CInt(100 * ((CInt(TextBox79.Value) + CInt(TextBox80.Value) + CInt(TextBox81.Value) + CInt(TextBox82.Value) + CInt(TextBox83.Value)) / 100))
    'MsgBox subGradenum

End Select

Select Case subGradenum

Case 0 To 40
    subGradeArray(i) = "C"
    
    Case 41 To 50
    subGradeArray(i) = "B"
    
    Case 51 To 70
    subGradeArray(i) = "B+"
    
    Case 71 To 90
    subGradeArray(i) = "A"
    
    Case 91 To 100
    subGradeArray(i) = "A+"
    
End Select
'MsgBox subGradenum & ", " & subGradeArray(i)
Next i

 

[RickXL]

Hi,

Your code looks OK to me, but I haven't tried it.

I suspect that the TextBoxes are not laid out exactly as you think they are. You are using at least 84 TextBoxes by the looks of things and I suspect that the occasional one has been misplaced.

One way to check would be to insert, say, 100 in boxes 7, 13, 20, 27, 33 and 40 and then run the code. If results are as expected then move to the second column of TextBoxes: 8, 14, 21 etc and repeat for all columns.

Any that produce erroneous results will need to be checked to see if the correctly numbered TextBox is in the right place in the layout.

 

[nadiminti]

Thank you. These text boxes are on UserForm. The textbooks are properly aligned and the data is being stored exactly where I need.

The problem started when I tried to implement the grades formula.

Hi,

Your code looks OK to me, but I haven't tried it.

I suspect that the TextBoxes are not laid out exactly as you think they are. You are using at least 84 TextBoxes by the looks of things and I suspect that the occasional one has been misplaced.

One way to check would be to insert, say, 100 in boxes 7, 13, 20, 27, 33 and 40 and then run the code. If results are as expected then move to the second column of TextBoxes: 8, 14, 21 etc and repeat for all columns.

Any that produce erroneous results will need to be checked to see if the correctly numbered TextBox is in the right place in the layout.

 

[RickXL]

So are you saying that the final MsgBox always produces consistent answers or not?

MsgBox subGradenum & ", " & subGradeArray(i)

Or is it that subGradenum is not what you expected?

 

[nadiminti]

Great... your answer - "Consistent" gave me idea and found that code is correct but not the data input. So the problem is solved.

Can you help solve the next problem I'm facing. If the data entered is null in any of the text boxes, Cint triggers an error and asks the user to debug.
The data could be blank per the requirement.
How to trap the error?

 

[RickXL]

I am not quite sure what all the CInt functions are for. Will people be entering decimal numbers that need to be rounded?

If not then you could try replacing all the pieces of code like this:

CInt(TextBox7.Value)

with ones like this:

Val(TextBox7.Value)

If you need to keep all the CInts then you could add Val as well:

CInt(Val(TextBox7.Value))

 

[nadiminti]

It worked like a charm. CInt returns the Integer value of the TextBox. (found some where in Mr. Excel Forum)

Thank you