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

Question

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.

I would reverse the order, too, with the criteria for an "A" on top. Otherwise you might stop short and give everyone a "D" without the chance to see if they earned a higher grade. This is only necessary if you decide to use Else Ifs. — Matt Cremeens
– Matt Cremeens, Commented Jan 21, 2020 at 15:48
This URL explains why to use "End If": learn.microsoft.com/en-us/dotnet/visual-basic/… — Dominique
– Dominique, Commented Jan 21, 2020 at 15:50
When you get the syntax for the If, elseif, else,end if correct you will find that your code only assigns a D or an F. This is because the If , then ,elseif stops at the first satisfied condition, so because an A is > 90 it is also greater than 60,therefore >90 satisfies the first condition , which is > 60, therefore >90 will be allocated a D. I'm not sure if this will make you popular with your students!!. This is why a previous commenter suggested to put the a grading first i.e. test for A, B, C, D then F because >90 is greater than 80-89 etc. — freeflow
– freeflow, Commented Jan 21, 2020 at 15:56

Pᴇʜ · Accepted Answer · 2020-01-21 16:03:20Z

7

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

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:

edited Jan 21, 2020 at 16:03

answered Jan 21, 2020 at 15:56

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

2 Comments

Austin G Over a year ago

I normally would've done that as I've used this technique in the past. My professor required me to use a button and VBA to do the work this time.

Siddharth Rout Over a year ago

Nice :) @AustinG you can use VBA to enter this formula in all that range in one go. rng.Formula = Above formula and then rng.Value = rng.Value. Just two lines of code...

Zwenn · Accepted Answer · 2020-01-21 16:16:19Z

6

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

edited Jan 21, 2020 at 16:16

answered Jan 21, 2020 at 15:58

Zwenn

2,2672 gold badges10 silver badges15 bronze badges

3 Comments

Plutian Over a year ago

Great answer, but you have fallen for the same pitfall as OP as pointed out by Matt Creemens. It is important you start with the highest number first, or the first condition will resolve as TRUE for everything above 60, and they will all get a "D".

Zwenn Over a year ago

@Plutian Argh, you're right. I've edited it. In Java, you need a break for every case. Otherwise, the next case is checked, too. In VBA it's different, I hadn't thought of that :-)

Plutian Over a year ago

Yeah, VBA is much lazier than that. If it encounters a True it will immediately end the select case and be happily on its way.

jclasley · Accepted Answer · 2020-01-21 15:58:04Z

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

Warcupine · Accepted Answer · 2020-01-21 18:52:30Z

4

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

edited Jan 21, 2020 at 18:52

answered Jan 21, 2020 at 15:50

Warcupine

4,7203 gold badges18 silver badges27 bronze badges

3 Comments

Austin G Over a year ago

Ive tried a few of the changes you suggested, I didnt add Worksheets() in yet. I am still getting an error about the Next i line, "Next without for" is the error message.

Warcupine Over a year ago

Did you change the ifs (aside from the first) to elseifs and did you change exit to end if?

jclasley Over a year ago

@AustinG make sure you have Then at the end of every line except for the Else line. That error text is often thrown when dropping a Then

JvdV · Accepted Answer · 2020-01-21 18:29:32Z

2

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 =)

edited Jan 21, 2020 at 18:29

answered Jan 21, 2020 at 16:38

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

1 Comment

Siddharth Rout Over a year ago

Another nice approach :)

Austin G · Accepted Answer · 2020-01-21 16:21:15Z

0

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

answered Jan 21, 2020 at 16:21

Austin G

411 bronze badge

2 Comments

basic Over a year ago

Don't forget to check the best answer.

Plutian Over a year ago

If your issue is resolved, please mark the answer which was most useful to you as accepted (grey tick next to the answer) to show your appreciation for their hard work.

Collectives™ on Stack Overflow

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

6 Answers 6

2 Comments

3 Comments

Comments

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

3 Comments

Comments

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related