hlookup with combobox in userform runtime error.

[KestutisTower]

Hello, I'm new to VBA. I have written a code that hlookop value in table when I press option button .

Sub XS()


  If UserForm1.ComboBox1.Value = "" Then
   Worksheets("Sheet1").Range("E1").Value = 0
Else
  Worksheets("Sheet1").Range("E1").Value = Application.WorksheetFunction.HLookup(UserForm1.ComboBox1.Value, Juodrastis.Range("A1:D4"), 2, False)
End If

If UserForm1.txtSuma.Value = "" Then
   UserForm1.txtSuma.Value = 0
End If

Worksheets("Sheet1").Range("E1").Value = Worksheets("Sheet1").Range("E1").Value + UserForm1.txtSuma.Value
End Sub

Book123456.xlsm
	A	B	C	D	E
1		OMNIVA	Lpexpress	Pastas
2	XS	2.20 €	2.00 €	1.60 €
3	S	2.20 €	2.90 €	1.60 €
4	M	2.90 €	3.40 €	2.50 €
Sheet1

code runs if nothing is selected in combobox, and it displays 0 in "E3". But if there is value in combobox1, code stops in line #4. and says 'object required'.
I'm runnig code from module. code in userform is this:

Dim a As Variant      'At the beginning of all the code.

Private Sub cmbAtnaujinti_Click()


Result = MsgBox("Kliento informacija bus atnaujinta", vbOKCancel + vbQuestion)
If Result = vbOK Then
Call Atnaujinti

Else
End If
End Sub

Private Sub ComboBox1_Change()
  Call FilterData
  Call KlientoInfo
  Call Suma
End Sub

Private Sub ComboBox2_Change()
  Call FilterData
  Call Suma
End Sub

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant
  Dim i As Long
  
  TextBox1 = ""
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 1) Else cmb1 = ComboBox1.Value   '1 = column A - names
    If ComboBox2.Value = "" Then cmb2 = a(i, 11) Else cmb2 = ComboBox2.Value  '11 = column K - dates
    If a(i, 1) = cmb1 And a(i, 11) = CDate(cmb2) Then                         '1 = A, 11 = K
       TextBox1.Value = TextBox1.Value & a(i, 10) & vbCr                      '10 = J
    End If
  Next
End Sub

Private Sub optXS_Click()
Call XS
End Sub

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim dic1 As Object, dic2 As Object
  Dim i As Long
  
  Set sh = Sheets("Pardavimai")     'Adjust to the name of your sheet.
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  dic1.comparemode = vbTextCompare
  a = sh.Range("A2:K" & sh.Range("J" & Rows.Count).End(3).Row).Value
  
  'To load data into combos 1 and 2
  For i = 1 To UBound(a)
    dic1(a(i, 1)) = Empty       '1 = column A - names
    dic2(a(i, 11)) = Empty      '11 = column K - dates
  Next
  ComboBox1.List = Application.Transpose(dic1.keys)
  ComboBox2.List = Application.Transpose(dic2.keys)
End Sub

Private Sub cmdIstrinti_Click()

  Call Reset
  
End Sub

Private Sub cmdOK_Click()

  Call Submit
  Call enterKlientai
  Call Reset

End Sub

Private Sub UserForm_Initialize()

 Call Reset
  
End Sub

Private Sub txtPrekesNr_Change()

   If txtPrekesNr.Value = "" Then
      txtPreke.Value = ""
   End If
   
   If IsNumeric(txtPrekesNr.Value) Then
   txtPreke.Value = WorksheetFunction.VLookup(CLng(txtPrekesNr.Value), Sheets("Eiliskumas").Range("B4:D100"), 2, False)
   txtVienetoKaina.Value = WorksheetFunction.VLookup(CLng(txtPrekesNr.Value), Sheets("Eiliskumas").Range("B4:D100"), 3, False)
   End If

End Sub

Please help! I'm stuck. I did recreate this code in other workbook, less complicated. to test it. and it runs fine.. I'm lost here. must be something I'm not seeing.

 

[DanteAmor]

Worksheets("Sheet1").Range("E1").Value = Application.WorksheetFunction.HLookup(UserForm1.ComboBox1.Value, Juodrastis.Range("A1:D4"), 2, False)

What is "Juodrastis", is the codename of your sheet?

 

[KestutisTower]

What is "Juodrastis", is the codename of your sheet?

Hi, it is name of the sheet that the table with lookop values is.

 

[DanteAmor]

Try:

Worksheets("Sheet1").Range("E1").Value = Application.WorksheetFunction.HLookup(UserForm1.ComboBox1.Value, Sheets("Juodrastis").Range("A1:D4"), 2, False)

 

[KestutisTower]

Try:

Worksheets("Sheet1").Range("E1").Value = Application.WorksheetFunction.HLookup(UserForm1.ComboBox1.Value, Sheets("Juodrastis").Range("A1:D4"), 2, False)

Now it brings runtime error '1004'. Unable to get Hlookup property of the WorksheetFunction class. Also tried to run it straight from userform. Same.

 

[KestutisTower]

Now it brings runtime error '1004'. Unable to get Hlookup property of the WorksheetFunction class. Also tried to run it straight from userform. Same.

OK. my bad... I was looking for wrong thing. instead of combobox1 had to betxtbox value.

Sub XS()


  If UserForm1.txtSiuntimas.Value = "" Then
   Worksheets("Juodrastis").Range("H3").Value = 0
Else
  Worksheets("Juodrastis").Range("H3").Value = Application.WorksheetFunction.HLookup(UserForm1.txtSiuntimas.Value, Sheets("Juodrastis").Range("B1:D4"), 2, False)
End If

If UserForm1.txtSuma.Value = "" Then
   UserForm1.txtSuma.Value = 0
End If

Worksheets("Juodrastis").Range("H3").Value = Worksheets("Juodrastis").Range("H3").Value + UserForm1.txtSuma.Value
End Sub

 

[DanteAmor]

I'm glad to hear it's working for you now.