Lookup values from two combobox on a userform

[jooz56]

Hi
Im trying to lookup two values that come from two comboboxes of a userform and the result to appear in a textbox

The values of the comboboxes are related to two different name ranges on my sheet 2 and the table array for the lookup formula is in the same sheet (sheet2)

I want that a button on the userform when clicked show me the lookup result from these two comboboxes on a texbox and if the combination ot this two comboboxes doesn´t match then send me a message that there is no match.

My first combobox has a list of products and the second a list of countries. My data has four columns the first its a help column to make the lookup the second contains a product, the third a country and the fourth a regulation that applies depending of the combination of the first and second column.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]helper[/TD]
[TD]product[/TD]
[TD]conuntry[/TD]
[TD]regulartion[/TD]
[/TR]
[TR]
[TD]tomatospain[/TD]
[TD]tomato[/TD]
[TD]spain[/TD]
[TD]regulation 1[/TD]
[/TR]
[TR]
[TD]avocadoUSA[/TD]
[TD]avocado[/TD]
[TD]USA[/TD]
[TD]regulation 3[/TD]
[/TR]
[TR]
[TD]blueberryjapan[/TD]
[TD]blueberry[/TD]
[TD]japan[/TD]
[TD]regulation 9[/TD]
[/TR]
</tbody>[/TABLE]

Private Sub CommandButton1_click() 

txtregulation = Application.WorksheetFunction.VLookup(ComboBox1.value & ComboBox2.value, Sheet1.Range("tabla"), 4, False)

i tried with this code for the vlookup formula but the result doesnt appear

help please!!!

 

[Fluff]

How about

Option Explicit
Private Dic As Object

Private Sub CommandButton1_Click()
   Dim ValU As String
    ValU = Dic(Me.[COLOR=#ff0000]ComboBox1[/COLOR].Value & Me.[COLOR=#ff0000]ComboBox2[/COLOR].Value)
    If ValU = "" Then MsgBox "No Value"
    Me.[COLOR=#ff0000]TextBox1[/COLOR].Value = ValU
End Sub

Private Sub UserForm_Initialize()

   Dim v1 As String
   Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare

   With Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
      For Each Cl In .Range("B2", .Range("B" & Rows.count).End(xlUp))
         v1 = Cl.Value & Cl.Offset(, 1).Value
         If Not Dic.exists(v1) Then Dic.Add v1, Cl.Offset(, 2).Value
      Next Cl
   End With
End Sub

This doesn't need a helper column.
Change values in red to suit

 

[jooz56]

thanks for the reply

i tried this code but it only shows a msgbox "no value". my data is on sheet 1 the table array is on A1 to C9. I filled the two comboboxes so the values match with my third column (C) but it shows "No value"

what other option can i try ?

 

[Fluff]

If combobox1 values are in col A & combobox2 values are in col B try

Private Sub UserForm_Initialize()

   Dim v1 As String
   Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare

   With Sheets("Sheet1")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         v1 = Cl.Value & Cl.Offset(, 1).Value
         If Not Dic.exists(v1) Then Dic.Add v1, Cl.Offset(, 2).Value
      Next Cl
   End With
End Sub

Also these 2 lines

Option Explicit
Private Dic As Object

must be at the very top of the userform module(ie before any code)