Excel VBA Macros

Question

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

mark = Range("B1:B4").Value you set mark as int then try to store array to it. Edit: you can do a for each e in mark followed by your case statement. — findwindow
– findwindow, Commented May 3, 2016 at 15:35

SierraOscar · Accepted Answer · 2016-05-03 16:15:31Z

2

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

edited May 3, 2016 at 16:15

answered May 3, 2016 at 15:49

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

re: formula based solution: I'm pretty certain that the "No text entered" will never be reached unless column B has a zero-length string returned by a formula. A truly blank cell would be a "Fail".

findwindow Over a year ago

^ Oh snap. Removing my upvote. Edit: this is what you get for being snobby =P

user4039065 · Accepted Answer · 2016-05-03 15:51:43Z

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.

interesting-name-here · Accepted Answer · 2016-05-03 17:45:05Z

2

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

edited May 3, 2016 at 17:45

answered May 3, 2016 at 15:46

interesting-name-here

1,8901 gold badge22 silver badges35 bronze badges

1 Comment

interesting-name-here Over a year ago

@Jeeped - declared variables :-P

Collectives™ on Stack Overflow

Excel VBA Macros

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related