Quick Case Statement

[sammclean23]

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.

[vba]
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
[/vba]

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

[sammclean23]

I do apologise but I have managed to modify the IF statement to function as intended:

Here is what I used:

[VBA]
Sub EXAM_GRADES()
Dim GradeRange As Integer
'Set graderange as the number of marks

Worksheets("Sheet1").Activate 'Activate Sheet1
Range("A1").Activate 'Activate cell A1
Do Until IsEmpty(ActiveCell.value)
GradeRange = ActiveCell.value


If GradeRange >= 0 And GradeRange <= 39 Then
ActiveCell.Offset(0, 1).value = "Fail"

ElseIf GradeRange >= 40 And GradeRange <= 54 Then
ActiveCell.Offset(0, 1).value = "Pass"

ElseIf GradeRange >= 55 And GradeRange <= 69 Then
ActiveCell.Offset(0, 1).value = "Merit"

ElseIf GradeRange >= 70 And GradeRange <= 100 Then
ActiveCell.Offset(0, 1).value = "Distinction"

Else
ActiveCell.Offset(0, 1).value = "Unknown"

End If

ActiveCell.Offset(1, 0).Activate
Loop
End Sub
[/VBA]

Again, I do apologise for wasting a thread.

[Paul_Hossler]

Select Case statements are pretty easy to use, when you get the hang of the syntax

[VBA]
Option Explicit

Sub EXAM_GRADES()

Worksheets("Sheet1").Select
Range("A1").Select


Do Until IsEmpty(ActiveCell)

Select Case ActiveCell.Value

Case 0 To 39
ActiveCell.Offset(0, 1).Value = "Fail"

Case 40 To 54
ActiveCell.Offset(0, 1).Value = "Pass"

Case 55 To 69
ActiveCell.Offset(0, 1).Value = "Merit"

Case 70 To 100
ActiveCell.Offset(0, 1).Value = "Distinction"

Case Else
ActiveCell.Offset(0, 1).Value = "Unknown"

End Select

ActiveCell.Offset(1, 0).Activate
Loop
End Sub
[/VBA]

Paul