VLookup in userform with Combox as look up value

[Mikeymike_W]

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.

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

 

[DanteAmor]

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:

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

 

[Mikeymike_W]

Hi Dante,

This worked a treat, many thanks for your help.

Mike

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.