VLookup in userform with Combox as look up value

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi,

I have a userform and i want to populate a frame with text from a worksheet called "interpretations" using a command button to reveal.

I have two comboboxes (cboxmajor and cboxaoi2) and the value of these combined is what i want the VLookup to search for then use the column next to that value to extract the text and populate the frame in the userform (hope that makes sense).

I used the following code which i found online but no idea if i'm using it right. I put it in the command button code.

Code:
Dim myLookupValue As String    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim myColumnIndex As Long
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myVLookupResult As Long
    Dim myTableArray As Range
    
    myLookupValue = Cboxmajor.Value & " " & Cboxaoi2
    myFirstColumn = 1
    myLastColumn = 80
    myColumnIndex = 2
    myFirstRow = 2
    myLastRow = 25


    With Worksheets("Interpretations")
        Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
    End With
    
    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)




    Frame1.Value = Format(myVLookupResult, "#,##0")

Thanks in advance for any help you can give me,

Mike
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
You could give an example of what you have in the combos and what you have on the sheet.


Do you want to put the data from column B in the caption of the frame?

Try this:

Code:
Private Sub CommandButton1_Click()
    Dim s As Worksheet, myValue As String, f As Range
    Set s = Worksheets("Interpretations")
    myValue = Cboxmajor.Value & " " & Cboxaoi2.Value
    Set f = s.Range("A:A").Find(myValue, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        Frame1.Caption = f.Offset(0, 1).Value
    Else
        MsgBox "Does not exists : " & myValue
    End If
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,989
Messages
5,639,405
Members
417,086
Latest member
bfruge

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