User Form and VLookup

[Buskopan]

Helol Again!

Trying to understand how that works with userform.

I got combobox1 on user form which takes the names range from the list Database. I want textbox3 to vlookup for the positions. But getting the "424 object required error"

Range "POBALL" is 4 column.

What is wrong with the code ?

Private Sub UserForm_Initialize()
    Me.TextBox1 = Date
    Me.TextBox2 = Time
    Me.TextBox3.Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet2.Range("POBALL"), 3, False)
   
End Sub

 

[Shweta]

Hi,

Issue is with the lookup range for Vlookup function. As you mentioned POBALL is column 4 only and you are looking for column number 3 in your formula. You cannot find 3rd column in a range which is limited to 1 column only.

Regards,
Shweta

 

[Buskopan]

Sorry .Maybe I explained wrong.
Combobox1 range is Range named POBnames. Once userform loads it is empty, user have to chose from the list the name. And then I want textbox3 to show the company.

List Database have 2 ranges. 1 Range is POBnames which is only 1 column and another one is POBALL which is 4 colums. Name, company, position, roomN. Forget about date and time.

Lets say I want textbox3 to show the company once combobox1 value selected in userform.

Private Sub UserForm_Initialize()
    Me.TextBox3.Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheets(Database).Range("POBALL"), 3, False)
End Sub

Why i'm getting Error 9 Subsript out of range ?
and

 

[Buskopan]

ok it works. there was someting wrong with useerform. created new one and it is ok with code