sammclean23
11-25-2011, 01:42 AM
Hi All,
Just trying to get a loop functioning and have been advised to use a CASE statement rather than IF statements.
What I am trying to do is as follows:
Note, the code does not work as intended, im hoping using cases will help solve this.
Sub EXAM_GRADES()
Dim GradeRange As Integer
'Set graderange as the number of marks
GradeRange = ActiveCell.value
Worksheets("Sheet1").Activate 'Activate Sheet1
Range("A1").Activate 'Activate cell A1
Do Until IsEmpty(ActiveCell.value)
ActiveCell.Offset(1, 0).Activate
If GradeRange >= 0 And GradeRange <= 39 Then
ActiveCell.Offset(0, 1) = "Fail"
ElseIf GradeRange >= 40 And GradeRange <= 54 Then
ActiveCell.Offset(0, 1) = "Pass"
ElseIf GradeRange >= 55 And GradeRange <= 69 Then
ActiveCell.Offset(0, 1) = "Merit"
ElseIf GradeRange >= 70 And GradeRange <= 100 Then
ActiveCell.Offset(0, 1) = "Distinction"
Else
ActiveCell.Offset(0, 1) = "Unknown"
End If
Loop
That is what I have using IF statements, now I need to do the same using CASES.
The code above simply:
- Checks the mark
- if the mark is between any of the ranges defined above, it puts the appropriate grading the next column
- loops until an empty field is found.
I have looked up using CASE statements but not sure how I would code it, any help would be appreciated!
Thanks,
Sam
Just trying to get a loop functioning and have been advised to use a CASE statement rather than IF statements.
What I am trying to do is as follows:
Note, the code does not work as intended, im hoping using cases will help solve this.
Sub EXAM_GRADES()
Dim GradeRange As Integer
'Set graderange as the number of marks
GradeRange = ActiveCell.value
Worksheets("Sheet1").Activate 'Activate Sheet1
Range("A1").Activate 'Activate cell A1
Do Until IsEmpty(ActiveCell.value)
ActiveCell.Offset(1, 0).Activate
If GradeRange >= 0 And GradeRange <= 39 Then
ActiveCell.Offset(0, 1) = "Fail"
ElseIf GradeRange >= 40 And GradeRange <= 54 Then
ActiveCell.Offset(0, 1) = "Pass"
ElseIf GradeRange >= 55 And GradeRange <= 69 Then
ActiveCell.Offset(0, 1) = "Merit"
ElseIf GradeRange >= 70 And GradeRange <= 100 Then
ActiveCell.Offset(0, 1) = "Distinction"
Else
ActiveCell.Offset(0, 1) = "Unknown"
End If
Loop
That is what I have using IF statements, now I need to do the same using CASES.
The code above simply:
- Checks the mark
- if the mark is between any of the ranges defined above, it puts the appropriate grading the next column
- loops until an empty field is found.
I have looked up using CASE statements but not sure how I would code it, any help would be appreciated!
Thanks,
Sam