Userform VLOOKUP dependant on whether the number entered is in between a certain range

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
I will try to explain what I'm after as simply as possible.

Lets say i have 4 fields in my table;

A - Number From
B - Number To
C - Colour
D - Fruit

Example data could be:

A - 1
B- 10
C - Green
D - Apple

Then on a userform, i have 3 textboxes, one for a number, one for colour and one for fruit. The idea is the user will enter the number then the next two boxes will be looked up.

How do i perform a vlookup for the colour and fruit, dependant if the number the user enters in the first text box.

For example, if the number entered between 1-10 could be green apple, 11-20 could be yellow banana, etc.

Hope that makes sense. Basically need to perform a vlookup dependant on the range in which the number the user enters falls in.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Mageeg,

I mocked some sample data with a start and end number (ID in my code) and created a simple userform to test it. When I type in a number to textbox1 it looks up the ID in column 'start' to see if it is '>=' to it and then checks 'End' to see it is '<' this number. If both criteria are true then it returns the colour and fruit in my userform.

To trigger this I am using the 'Change' event for textbox1. Whenever a number is entered or deleted from this box the code will run and return the results.

My example below shows apple and green for the Number 12.

1623021931315.png


Code as follows for the lookup as well as the initalize to reset the textbox to blank and the labels to blank as well.

VBA Code:
Private Sub TextBox1_Change()

Dim ID As Integer
Dim StartID As Integer
Dim EndID As Integer

If TextBox1.Value = "" Then

Else

    ID = TextBox1.Value
    
        For Each i In Sheet2.Range("Table1[Start]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            Label1.Caption = i.Offset(0, 2).Value
            Label2.Caption = i.Offset(0, 3).Value
            
            End If
        
        Next i

End If

End Sub

Private Sub CommandButton1_Click()

UserForm1.Hide

End Sub

Private Sub UserForm_Initialize()

TextBox1.Value = ""
Label1.Caption = ""
Label2.Caption = ""

End Sub

Steven
 
Upvote 0
Solution
For Each i In Sheet2.Range("Table1[Start]")

Hi Steven, thank you so much, this is exactly the sort of thing I am after. However i am struggling to get it to work. Can you explain this line of code for me? Can i change sheet2 to whatever my sheet name is called?

I am receiving the error variable not define on i, (i am a newbie sorry!)

For added context here is an image of my table, then how i changed the code:


1623026610929.png


VBA Code:
Dim ID As Integer
Dim StartID As Integer
Dim EndID As Integer


If TextBox4.Value = "" Then

Else

    ID = TextBox4.Value
    
        For Each i In ReceiverData.Range("Table1[From]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            TextBox5.Value = i.Offset(0, 3).Value
            TextBox6.Value = i.Offset(0, 4).Value
            TextBox7.Value = i.Offset(0, 5).Value
            
            End If
        
        Next i

End If
 
Upvote 0
Nearly there then.

What my code 'Sheet2.Range("Table1[Start]")' is saying is that on sheet 2 I have a table called "Table1" and there is a column called "Start" that I want to look at in my macro. The below will create a table in your workbook to use in the macro.

Have assumed that your worksheet is called 'ReceiverData' from your update in the code.

So first up...
  1. select the data in your worksheet A1:E6
  2. click on Insert> Table (greyed out in my image below)
  3. make sure 'My table has headers' is ticked
  4. click OK
1623030216198.png


This will now create a table in the workbook which will look similar to the below with coloured rows and a little bold corner on the bottom right of it.
1623030232065.png


Click on the little white triangle indicated below and list will show, hopefully it will only have Table1 in yours. If not click on the last one and it should highlight your data. If not click on each one until it does. The one that highlights your data is the table name/number we need.
1623030394450.png


So in the code you have I have changed it to look at sheet name 'ReceiverData' and to look at 'Table1' (change the number if it is different) and to look at column 'From'.

I have also change the data type for the ID etc. to Long.

VBA Code:
Sub LookupDetails()

Dim ID As Long
Dim StartID As Long
Dim EndID As Long


If TextBox4.Value = "" Then

Else

    ID = TextBox4.Value
    
        For Each i In Sheets("ReceiverData").Range("Table1[From]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            TextBox5.Value = i.Offset(0, 3).Value
            TextBox6.Value = i.Offset(0, 4).Value
            TextBox7.Value = i.Offset(0, 5).Value
            
            End If
        
        Next i

End If

Should do the trick.

Steven
 
Upvote 0
If ID >= StartID And ID < EndID Then TextBox5.Value = i.Offset(0, 3).Value TextBox6.Value = i.Offset(0, 4).Value TextBox7.Value = i.Offset(0, 5).Value

Hi Steven thank you! this looks like it should work,

However upon entering the number in the textbox, I still get compile error: variable not defined. And it highlights the i in "For Each i" and the Private Sub Textbox4_AfterUpdate() in yellow.

One other thing, is the above quoted code correct If i wanted to put the lookup values into textboxes instead of labels?

Thanks!
 
Upvote 0
Hi Steven thank you! this looks like it should work,

However upon entering the number in the textbox, I still get compile error: variable not defined. And it highlights the i in "For Each i" and the Private Sub Textbox4_AfterUpdate() in yellow.

One other thing, is the above quoted code correct If i wanted to put the lookup values into textboxes instead of labels?

Thanks!
I think that's because you haven't defined variable i. When option explicit is on, you have to define each variable. Define i as variant or rangei .e,

Dim i as Range

That should work. Try
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top