VBA Lookup and like function

MrsK

New Member
Joined
Jan 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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 percentageStudent Name
100John
4Penny
51Bob

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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
 
Solution

MrsK

New Member
Joined
Jan 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,822
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top