Problem with data type when using cell reference

[kpku2020]

Hi,

I am trying to create a UDF for calculating grades. Everything works fine if I hard code the values(marks obtained and grades e.g. 4.5, "A+") in VBA code.

Problem is when I replace the hardcoded value with cell reference. I think I am not referencing the cells right or the data type used is not correct. I get #Value error or the results are incorrect.

In Marks Obtained I can have number from 0 to 5 including fractions like 1.5, 2.77, 3.75

Code is:

Public Function TGrade(MarksObtained As Double) As String
' THIS WORKS
  Select Case MarksObtained
  
  Case Is > 4.45
  TGrade = Sheets("Settings").Range("Z3").Value
  Case Is > 3.7
  TGrade = Sheets("Settings").Range("Z4").Value
  Case Is > 2.75
  TGrade = Sheets("Settings").Range("Z5").Value
  Case Is > 1.75
  TGrade = Sheets("Settings").Range("Z6").Value
  Case Else
  TGrade = Sheets("Settings").Range("Z7").Value
  
  End Select
'THIS DOESNT WORK
'  Select Case MarksObtained
'
'  Case Is >= Sheets("Settings").Range("P4").Value
'
'  TGrade = Sheets("Settings").Range("Z3").Value
'
'  Case Is >= Sheets("Settings").Range("P5").Value
'
'  TGrade = Sheets("Settings").Range("Z4").Value
'
'  Case Is >= Sheets("Settings").Range("P6").Value
'
'  TGrade = Sheets("Settings").Range("Z5").Value
'
'  Case Is >= Sheets("Settings").Range("P7").Value
'
'  TGrade = Sheets("Settings").Range("Z6").Value
'
'  Case Else
'
'  TGrade = Sheets("Settings").Range("Z7").Value
'
'  End Select
  
End Function

 

[NARAYANK991]

Hi ,

Try this :

Public Function TGrade(MarksObtained As Double) As String
                Application.Volatile
                With Sheets("Settings")
                    a = .Range("P4").Value
                    b = .Range("P5").Value
                    c = .Range("P6").Value
                    d = .Range("P7").Value
                End With
 
                Select Case MarksObtained
                      Case Is >= a
                            TGrade = Sheets("Settings").Range("Z3").Value

                      Case Is >= b
                            TGrade = Sheets("Settings").Range("Z4").Value

                      Case Is >= c
                            TGrade = Sheets("Settings").Range("Z5").Value
 
                      Case Is >= d
                            TGrade = Sheets("Settings").Range("Z6").Value

                      Case Else
                            TGrade = Sheets("Settings").Range("Z7").Value
                End Select
End Function

Narayan

 

[Jon von der Heyden]

Hi

You can achieve the same result using a binary VLOOKUP; there is no need for a UDF. Please see the attached.

 

[kpku2020]

Hi Narayan & Jon,

The solution provided by Narayan seems to be working. And its working even without Application.volatile.

But I feel this soln (of assigning cell value to variables and then comparing the variables in case statement) is further complicating the already complicated nested case statement in my solution. Also if you can explain what difference it bring if I compare the cell values directly. i.e.

a = .Range("P4").Value
Case Is >= a
TGrade = Sheets("Settings").Range("Z3").Value

vs

' Case Is >= Sheets("Settings").Range("P4").Value''
TGrade = Sheets("Settings").Range("Z3").Value

----
Actually in my case I have a Grade Key table. Based on the "TotalMarks" in the top row I have to select the corresponding column and then find the relative Grade.
Thus in my case both Total Marks & MarksObtained are both dynamic.

Kindly check the file & suggest the best solution.