CODEWITHSUNDEEP
excelvba
Austin G
Austin G

Reputation: 41

Using "if then" statements inside a "for" loop in Microsoft VBA

I am trying to pull numerical grade values and convert them into a string which will represent the grade, A >90, B >80, and so on. I planned to use a for loop to gather the values from the spreadsheet and then if then statements to assign the letter designation. Here is the code I have so far.

 For i = 1 To 11
' Pull Grade numerical value
    Grade = Cells(2 + i, 16).Value
' Assign Numerical Value a script Grade
    If Grade > 60 Then
        Letter = "D"
    If Grade > 70 Then
        Letter = "C"
    If Grade > 80 Then
        Letter = "B"
    If Grade > 90 Then
        Letter = "A"
    Else
        Letter = "F"
    Exit
' Print the letter grade
    Cells(2 + i, 17).Text = Letter
    Next i

I keep getting errors either pertaining to "Exit" or to "Next i". I have tried using "End" statements as well but that didn't fix the problems either.

Upvotes: 4

Views: 627

Answers (6)

Warcupine
Warcupine

Reputation: 4640

You have a bunch of problems here.

First exit needs to be end if

Second You need elseif for multiple conditions in the same if statement.

Lastly you need to reorder your ifs. If you have a grade of 90 it would return a "D" as that is the first true statement it encounters.

You might as well just loop from 3 - 13 instead of adding 2 each time aswell. And make sure you use explicit references, it will eventually bite you.

I missed one thing, Make all your comparisons >= Folks won't be happy if their 90 is a B.

For i = 3 To 13
' Pull Grade numerical value
    Grade = Worksheets("YourSheetNameHere").Cells(i, 16).Value 'Change that sheet name
' Assign Numerical Value a script Grade
    If Grade >= 90 Then
        Letter = "A"
    elseIf Grade >= 80 Then
        Letter = "B"
    elseIf Grade >= 70 Then
        Letter = "C"
    elseIf Grade >= 60 Then
        Letter = "D"
    Else
        Letter = "F"
    End if
' Print the letter grade
    Worksheets("YourSheetNameHere").Cells(i, 17).value = Letter 'Change that sheet name
    Next i

Upvotes: 4

JvdV
JvdV

Reputation: 75840

Alternatively, use INDEX and MATCH as Application.Function:

Sub Test()

Dim Grade As Long: Grade = 55
Dim arr1 As Variant: arr1 = Array("A", "B", "C", "D", "F")
Dim arr2 As Variant: arr2 = Array(100, 89, 79, 69, 59)

With Application
    Debug.Print .Index(arr1, .Match(Grade, arr2, -1))
End With

End Sub

Or drop the INDEX function, and call the array directly:

With Application
    Debug.Print arr1(.Match(Grade, arr2, -1) - 1)
End With

Obviously implement this in your loop/function and write the returned value to your cells. Change Grade variable to see the different values this would return =)

Upvotes: 2

Austin G
Austin G

Reputation: 41

I changed the format of the code to match Jclasley and it works fine, here is my final code:

For i = 1 To 11
' Pull Grade numerical value
    Grade = Cells(2 + i, 16).Value
' Assign Numerical Value a script Grade
    Select Case Grade
        Case Is >= 90
            Letter = "A"
        Case Is >= 80
            Letter = "B"
        Case Is >= 70
            Letter = "C"
        Case Is >= 60
            Letter = "D"
        Case Else
            Letter = "F"
    End Select
    Cells(2 + i, 17).Value = Letter
    Next i
End Sub

Upvotes: 0

Zwenn
Zwenn

Reputation: 2267

I would prefer Select Case in this situation. And start the Loop from 3 so you don't need to add 2:

For i = 3 To 11
  ' Pull Grade numerical value
  Grade = Cells(i, 16).Value
  ' Assign Numerical Value a script Grade
  Select Case Grade
    Case Is >= 90: letter = "A"
    Case Is >= 80: letter = "B"
    Case Is >= 70: letter = "C"
    Case Is >= 60: letter = "D"
    Case Else: letter = "F"
  End Select
  ' Print the letter grade
  Cells(i, 17).Value = letter
 Next i

Upvotes: 6

Pᴇʜ
Pᴇʜ

Reputation: 57683

Or just use a formula in column Q:

=IF(P:P>=90,"A",IF(P:P>=80,"B",IF(P:P>=70,"C",IF(P:P>=60,"D","F"))))

so it updates automatically and you don't need to use VBA.

Alternatively you can add a sheet named GradeList with the following data

enter image description here

and use

=INDEX(GradeList!B:B,MATCH(P:P,GradeList!A:A,1))

as formula. This way you can easily edit the numbers/grades later, and it will pick the correct grades automatically:

enter image description here

Upvotes: 7

jclasley
jclasley

Reputation: 678

Great answers from everyone, but surprised not to see a Select...Case mentioned here. This is a perfect example, perhaps even textbook.

Select...Case evaluates a variable according to a number of criteria and then does whatever is written inside the case statement.

So your code would look like this:

For i = 3 To 13
' Pull Grade numerical value
    Grade = Worksheets("YourSheetNameHere").Cells(i, 16).Value 'Change that sheet name
    'do our evaluation based on "grade"
    Select Case Grade
        Case >= 90
            Letter = "A"
        Case >= 80
            Letter = "B"
        Case >= 70
            Letter = "C"
        Case >= 60
            Letter = "D"
        Case Else
            Letter = "F"
    End Select

Next i

Upvotes: 5

Related Questions