VBA Lookup and like function

[MrsK]

Hello,

I'm trying to create a VBA code that looks ups a grade number and retrieves the name associated with that number, however, the current code I've written has a 'like' function that I don't quite understand and means that a student that receives a 4% will appear for lookups values 4 and 10-39. I hope this makes sense and I appreciate any help you can give. Here is an example of my table:

Grade as percentage	Student Name
100	John
4	Penny
51	Bob

Here is my code:

Option Explicit

Public Function look2(ByVal Lookup_Value As String, ByVal Cell_range As Range, ByVal Column_Index As Integer) As Variant
Dim cell As Range
Dim Result_String As String

On Error GoTo errHandle

For Each cell In Cell_range

If cell.Value >= Lookup_Value And cell.Value < Lookup_Value + 1 Then

If cell.Offset(0, Column_Index - 1).Value <> "" Then

If Not Result_String Like "*" & cell.Offset(0, Column_Index - 1).Value & "*" Then
Result_String = Result_String & "," & cell.Offset(0, Column_Index - 1).Value
End If

End If

End If

Next cell

look2 = LTrim(Right(Result_String, Len(Result_String) - 1))

Exit Function

errHandle:
look2 = ""

End Function

 

[AlphaFrog]

In your code, the Like portion of the code is not doing the grade comparison. It is eliminating duplicate names (which you may not need or want).

The problem is this; Lookup_Value As String
You are comparing the text value 4 to the numeric values in the column. That will return odd results.

The simple fix is this to compare numeric 4 to the grade values.
Lookup_Value As Single

 

[MrsK]

In your code, the Like portion of the code is not doing the grade comparison. It is eliminating duplicate names (which you may not need or want).

The problem is this; Lookup_Value As String
You are comparing the text value 4 to the numeric values in the column. That will return odd results.

The simple fix is this to compare numeric 4 to the grade values.
Lookup_Value As Single

Perfect! Thank you so much AlphaFrog!