I want to find grades based on cell value

Question

I am using the following code but it's showing only "FAIL" instead of giving grades.

Sub TABLEGRADESCELLREF()

Worksheets("TABLE").Activate
Range("C61").Select

Do Until ActiveCell.Offset(0, -2).Value = ""
ActiveCell.Formula = "=IF(R[0]C2=R61..CF DRT56Y7"
ActiveCell.Offset(1, 0).Select

Loop

End Sub

I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jan 07 '19 at 13:25

score 0 · Answer 1 · answered Jan 07 '19 at 12:35

This is the code which should work for your exact example. As you don't give any more details, it is on you to adjust the code for your needs. Hope it helps you.

Sub TABLEGRADESCELLREF()

Dim rownumber As Long


With ActiveSheet

For rownumber = 61 To 66
    Select Case .Cells(rownumber, 2).Value
        Case .Cells(rownumber, 2).Value < 50
            .Cells(rownumber, 3).Value = "Fail"
        Case 50 To 59
            .Cells(rownumber, 3).Value = "D"
        Case 60 To 69
            .Cells(rownumber, 3).Value = "C"
        Case 70 To 89
            .Cells(rownumber, 3).Value = "B"
        Case .Cells(rownumber, 2).Value >= 90
            .Cells(rownumber, 3).Value = "A"
    End Select
Next

End With

End Sub

i am agree with you, its work perfectly, but i want to use cell references instead of giving value manually. instead of <50 writing i want to give cell reference "H66". For this what i have to do. Please help in this. Thanks for your support. — JPK, Jan 07 '19 at 14:07

score 0 · Accepted Answer · answered Jan 07 '19 at 13:39

You can use Vlookup function to get your desired result

Please see the inputs from the below picture

then, try with below code

Sub TABLEGRADESCELLREF()
    Dim i As Variant
    Dim lastrow As Variant
    Worksheets("TABLE").Activate
    lastrow = Range("C61").End(xlDown).Row
    For i = 61 To lastrow
        Range("E" & i).Value = Application.WorksheetFunction.VLookup(Range("D" & i), Range("$J$61:$L$66"), 3, True)
    Next i
End Sub

score 0 · Answer 3 · answered Jan 07 '19 at 13:45

Solution with formula only

Turn your grades/points into sorted from lowest points to highest points:
(0, 50, 60, 70, 90) This is necessary for MATCH to work. Also only use values no >= signs.
Use the following formula for your first grade:
=INDEX($I$60:$I$64,MATCH(B61,$H$60:$H$64,1))
Copy formula down to other grades.

The result is

I want to find grades based on cell value

3 Answers3

Solution with formula only