Reputation: 65
I have Range("A1:A4")
names and Range("B1:B4")
as grades. I need to their status in Range("C1:C4")
. I have used Select Case
in VBA for this, but it is throwing an error:
Runtime error 13 , type mismatch
Sub Grades()
Dim mark As Integer
mark = Range("B1:B4").Value
Select Case mark
Case 0 To 35
Range("C1:C4").Value = "Fail"
Case 36 To 59
Range("C1:C4").Value = "Pass"
Case 60 To 79
Range("C1:C4").Value = "Class"
Case 80 To 89
Range("C1:C4").Value = "Distinction"
Case 90 To 100
Range("C1:C4").Value = "Excellent"
Case Else
MsgBox "no text entered"
End Select
End Sub
Upvotes: 0
Views: 89
Reputation: 1890
Expanding on @findwindow's comment, here is code that will work.
Sub Grades()
Dim mark() As Variant
Dim Count As Variant
Dim m As Variant
Count = 1
mark = Range("B1:B4").Value
For Each m In mark
Select Case m
Case 0 To 35
Range("C" + Trim(Str(Count))).Value = "Fail"
Case 36 To 59
Range("C" + Trim(Str(Count))).Value = "Pass"
Case 60 To 79
Range("C" + Trim(Str(Count))).Value = "Class"
Case 80 To 89
Range("C" + Trim(Str(Count))).Value = "Distinction"
Case 90 To 100
Range("C" + Trim(Str(Count))).Value = "Excellent"
Case Else
MsgBox "no text entered"
End Select
Count = Count + 1
Next m
End Sub
Your Range("C1:C4").Value = "Fail"
, etc. will update the entire range of values. You only need to update one or else if the last person has a 99 then everyone gets an excellent.
The For Each
will cycle through each value and the Count
will update the correct C
cell
Upvotes: 2
Reputation: 17647
Range("B1:B4").Value
will return an array and therefore won't work with a Select Case
block.
You need to loop through each value in order to evaluate it separately:
For Each cell In Range("B1:B4").Cells
Select Case cell.Value
Case 0 To 35: Range("C" & cell.Row).Value = "Fail"
Case 36 To 59: Range("C" & cell.Row).Value = "Pass"
Case 60 To 79: Range("C" & cell.Row).Value = "Class"
Case 80 To 89: Range("C" & cell.Row).Value = "Distinction"
Case 90 To 100: Range("C" & cell.Row).Value = "Excellent"
Case Else: MsgBox "No text entered"
End Select
Next
Even better - would be simply assign a formula and re-write the value:
With Range("C1:C4")
.FormulaR1C1 = "=IF(RC[-1]>0,IF(RC[-1]<36,"Fail",IF(RC[-1]<60,"Pass",IF(RC[-1]<80,"Class",IF(RC[-1]<90,"Distinction",IF(RC[-1]<=100,"Excellent"))))),"No Value Entered")"
.Value = .Value
End With
Upvotes: 2
Reputation:
You are just trying to do four things at once. Use a loop to deal with each one at a time.
Sub Grades()
Dim mark As Integer, markrng As Range
For Each markrng In Range("B1:B4")
If CBool(Len(markrng.Value)) And IsNumeric(markrng.Value) Then
mark = CLng(markrng.Value)
Select Case mark
Case 0 To 35
markrng.Offset(0, 1) = "Fail"
Case 36 To 59
markrng.Offset(0, 1) = "Pass"
Case 60 To 79
markrng.Offset(0, 1) = "Class"
Case 80 To 89
markrng.Offset(0, 1) = "Distinction"
Case 90 To 100
markrng.Offset(0, 1) = "Excellent"
Case Else
MsgBox "not a valid mark in " & markrng.Address(0, 0)
End Select
Else
MsgBox "no mark entered in " & markrng.Address(0, 0)
End If
Next markrng
End Sub
To cover more potential errors, I've also added a check for non-numeric entries in addition to the missing grades.
Upvotes: 2