CODEWITHSUNDEEP
vbaloops
Petrie Manuel
Petrie Manuel

Reputation: 11

vba coding "do until or while" and "loop"

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

Upvotes: 0

Views: 192

Answers (3)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

user6432984
user6432984

Reputation:

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

Upvotes: 1

Related Questions