VBA iteration not returning value

Question

I am writing a very simple sub process to assign a letter grade to numeric grade values. I have a loop and I am trying to set the cell value to the output of my function. This seems like a very simple task but the first two iterations of my loops are not assigning any values. My Loop only goes through 4 rows.

Function get_letter(grade As Double)
    Select Case grade
        Case 0 To 59: letter = "F"
        Case 60 To 69: letter = "D"
        Case 70 To 79: letter = "C"
        Case 80 To 89: letter = "B"
        Case 90 To 100: letter = "A"
    End Select
    get_letter = letter
End Function

Sub assign_letter_grade()
    Dim x As Integer
    Dim grade As Range
    Dim letter As Range

    num_rows = Range("A2", Range("A2").End(xlDown)).Rows.Count

    Set grade = Range("J2")
    Set letter = Range("K2")

    For x = 1 To num_rows
        letter.Value = get_letter(grade.Value)
        Set grade = grade.Offset(1, 0)
        Set letter = letter.Offset(1, 0)
    Next
End Sub

Why don't this set the values of my first two rows in the loop?

What happens when you put =get_letter(J2) in K2 and fill down? Could the values in column J be text masquerading as numbers? — user4039065
– user4039065, Commented Jan 2, 2015 at 22:31
The value of J2 is a calculation - =IF(SUM(C2:F2)/400*0.2*100+MAX(G2:H2)/100*0.8*100>100, 100, SUM(C2:F2)/400*0.2*100+MAX(G2:H2)/100*0.8*100) — Ptrkcon
– Ptrkcon, Commented Jan 2, 2015 at 22:33
Just to be sure I casted the value using CDbl and still the same results — Ptrkcon
– Ptrkcon, Commented Jan 2, 2015 at 22:35
OT a bit - that formula might be better as =MIN(100, SUM(C2:F2)/400*0.2*100+MAX(G2:H2)/100*0.8*100)) — user4039065
– user4039065, Commented Jan 2, 2015 at 23:00

Gene Skuratovsky · Accepted Answer · 2015-01-02 22:55:59Z

3

Try this:

Function get_letter(grade As Double) As String
    if grade < 60 Then
        get_letter = "F"
    Elseif grade < 70 Then
        get_letter = "D"
    Elseif grade < 80 Then
        get_letter = "C"
    Elseif grade < 90 Then
        get_letter = "B"
    Else
        get_letter = "A"
    End If
End Function

answered Jan 2, 2015 at 22:55

Gene Skuratovsky

5812 silver badges6 bronze badges

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

Comments

Alex · Accepted Answer · 2015-01-02 22:42:17Z

1

The problem comes from the scores that is NOT included in the range. For VBA a score of 79.25 does not falls in either Case C or B. You could try to see if below would fix the problem:

Function get_letter(grade As Double)
Select Case grade
    Case 0 To 59.99: letter = "F"
    Case 60 To 69.99: letter = "D"
    Case 70 To 79.99: letter = "C"
    Case 80 To 89.99: letter = "B"
    Case 90 To 100.99: letter = "A" ' assuming student can get a score over 100
End Select
get_letter = letter
End Function

answered Jan 2, 2015 at 22:42

Alex

1,6321 gold badge12 silver badges30 bronze badges

3 Comments

Ptrkcon Over a year ago

This is working. Is there a better way to do the range change in the case statement? It just looks ugly to me.

Alex Over a year ago

There is Round() function which you can use on the scores before passing to the macro, where you can keep your original code, but depends on how you will define it... I mean, is 79.25 considered a B or C? (I would say B if i'm a student)

Alex Over a year ago

Look at Gene's answer, it might be a better choice

brettdj · Accepted Answer · 2015-01-03 13:29:59Z

1

Or using INDEX/MATCH

Function get_letter(grade As Double) As String
get_letter = Evaluate("INDEX({""F"",""D"",""C"",""B"",""A""},MATCH(" & grade & ",{0,60,70,80,90,100}))")
End Function

sample

Sub b()
Debug.Print get_letter(59.99)
Debug.Print get_letter(60)
End Sub

answered Jan 3, 2015 at 13:29

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Comments

SierraOscar · Accepted Answer · 2015-01-03 00:20:39Z

-1

Because you start at row 2:

Set grade = Range("J2")
Set letter = Range("K2")

and then immediately offset by 1 row in your loop:

Set grade = grade.Offset(1, 0)
Set letter = letter.Offset(1, 0)

therefore missing rows 1 to 2 out and starting at Row 3. Use the x variable in your loop to correct the problem:

For x = 1 To num_rows
    letter.Value = get_letter(grade.Value)
    Set grade = Range("J" & x)
    Set letter = Range("K" & x)
Next

answered Jan 3, 2015 at 0:20

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

VBA iteration not returning value

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related