0

I have 3 columns in my excel file: Name, Percentage, and Grade.

I want to automate the letter grade (e.g. percent of 95 will generate "A") to populate in the third column until the last row.

I keep getting an error related to how I loop this code. Any insight?

Sub Grades()
    Dim score As Integer

    Dim x As Integer
    x = 1
    score = Sheets("sheet1").Cells(x, 2).Value

    Do While score <> ""

        If score >= 90 And score <= 100 Then
            Sheets("Sheet1").Cells(x, 3).Value = "A"

        ElseIf score > 79 And score < 90 Then
            Sheets("Sheet1").Cells(x, 3).Value = "B"

        ElseIf score > 69 And score < 80 Then
            Sheets("Sheet1").Cells(x, 3).Value = "C"

        ElseIf score > 59 And score < 70 Then
            Sheets("Sheet1").Cells(x, 3).Value = "D"

        ElseIf score < 60 Then
            Sheets("Sheet1").Cells(x, 3).Value = "F"

        Else
            Sheets("Sheet1").Cells(x, 3).Value = ""

  End If

 x = x + 1
 score = Sheets("sheet1").Cells(x, 2).Value

 Loop
2
  • 1
    What error are you getting? Commented Aug 4, 2016 at 3:32
  • A Select Case would be better IMHO. Commented Aug 4, 2016 at 6:43

3 Answers 3

1

The problem is that score is a number and will always be <> "".

Sub Example2()
    Dim score As Integer
    Dim x As Integer
    x = 2

    With Sheets("sheet1")
        Do While .Cells(x, 2).Value <> ""

            score = .Cells(x, 2).Value

            If score >= 90 And score <= 100 Then
                .Cells(x, 3).Value = "A"

            ElseIf score > 79 And score < 90 Then
                .Cells(x, 3).Value = "B"

            ElseIf score > 69 And score < 80 Then
                .Cells(x, 3).Value = "C"

            ElseIf score > 59 And score < 70 Then
                .Cells(x, 3).Value = "D"

            ElseIf score < 60 Then
                .Cells(x, 3).Value = "F"

            Else
                .Cells(x, 3).Value = ""

            End If
            x = x + 1

        Loop

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

Comments

1

Petrie Manuel, I am not sure in what case you want to use your Else statement:

Sheets("Sheet1").Cells(x, 3).Value = "" 

Since if the score is less than 60 you will put an F, no ?

Anyway, to simplify your code and logic statements I used Select Case.

Sub Grades()

    Dim score       As Integer
    Dim x           As Integer
    Dim sht1        As Worksheet

    Set sht1 = ThisWorkbook.Worksheets("sheet1")

    ' if your first row is for table headers
    x = 2

    With sht1
        Do While .Cells(x, 2).Value <> ""
            score = .Cells(x, 2).Value

            Select Case score
                Case Is >= 90
                    .Cells(x, 3).Value = "A"

                Case Is >= 80
                    .Cells(x, 3).Value = "B"

                Case Is >= 70
                    .Cells(x, 3).Value = "C"

                Case Is >= 60
                    .Cells(x, 3).Value = "D"

                Case Is < 60
                     .Cells(x, 3).Value = "F"

                Case Else
                    .Cells(x, 3).Value = ""

            End Select

            x = x + 1
        Loop

    End With

End Sub

Comments

0

since:

  • numbers in column "B" are percentages -> always between 0 and 100

  • your need is parsing them by multiples of 10

then you could go like follows:

Option Explicit

Sub Grades()
    Dim cell As range
    With Sheets("scores") '<--| change "scores" with your actual sheet name
        For Each cell In .range("B1", .Cells(.Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers) '<--| loop through "numeric" column "B" cells down to last non empty one only
            cell.Offset(, 1) = Choose(Int(cell.value / 10) + 1, "F", "F", "F", "F", "F", "F", "D", "C", "B", "A", "A") 
        Next cell
    End With
End Sub

should your percentages come from formulas then simply substitute xlCellTypeConstants with xlCellTypeFormulas

2 Comments

@PetrieManuel: did you get through it?
@PetrielManuel: it'd be nice of you to give feedbakcs to people trying and help you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.