VBA – VLookup Match data in Userform Combobox and Automatically Populate Textboxes

[wcm69]

I’m really hoping someone can help me to modify the below code – which should automatically populate (with data from my spreadsheet: Sheet2) 4 Textboxes (Reg2 – Reg5) in my Userform AFTER matching the value (Full Name) in my Combobox (Reg1).

The issue is: Currently I am unable to get the

‘Application.WorksheetFunction.VLookup(CLng(Me.Reg1)’ to match TEXT values and NOT numbers – which I believe the ‘CLng’ function has been designed to do.

Unfortunately I only have a very limited knowledge of VBA and got the original code from a tutorial on the internet but I am unable to tweak it to suit my purposes.

Having done a bit of research it seems I possibly need to use a different function like CStr or CVar which I have tried in place of the CLng but with no success.

Please excuse my lack of VBA language / knowledge, but any help is always appreciated.
The code I’m using is:

Private Sub Reg1_AfterUpdate()
'Check to see if value exists

If WorksheetFunction.CountIf(Sheet2.Range("E:E"), Me.Reg1.Value) = 0 Then
MsgBox "This Name does not exist"

Me.Reg1.Value = ""
Exit Sub

End If

'Lookup values based on first control

With Me

.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("NINumber"), 2, 0)
.Reg3 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Trade2"), 3, 0)
.Reg4 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("UTRNumber"), 4, 0)
.Reg5 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("DayRate"), 5, 0)

End With

End Sub

Many thanks in advance

 

[Norie]

If you are looking for text values then you don't need any conversion function as the combobox contains text values.

With Me

    .Reg2 = Application.WorksheetFunction.VLookup(.Reg1.Value, Sheet2.Range("NINumber"), 2, 0)
    .Reg3 = Application.WorksheetFunction.VLookup(.Reg1.Value, Sheet2.Range("Trade2"), 3, 0)
    .Reg4 = Application.WorksheetFunction.VLookup(.Reg1.Value, Sheet2.Range("UTRNumber"), 4, 0)
    .Reg5 = Application.WorksheetFunction.VLookup(.Reg1.Value), Sheet2.Range("DayRate"), 5, 0) 

End With

 

[wcm69]

Hi Norie,

Thank you for taking the time to respond. Unfortunately when I've used the code you've supplied and test it in the Useform it comes back with a Run-time Error 1004 'Unable to get the V.Lookup property of the Worksheet Function Class"

Have you got any ideas what I might be doing wrong? I supplied the full code I'm using in my OP.

As usual, any help is greatly appreciated.

Thanks in advance

 

[Norie]

How are the named ranges NINumber, Trade2 etc defined?

Also, how are you populating the combobox?

 

[wcm69]

The named ranges are dynamic ranges (lists on Sheet2) created in the name manager. I'm not sure if I've got the named ranges defined.

I'm also populating the combobox from the properties row source.

Any ideas where I'm going wrong?

thanks

 

[Norie]

Are you populating the combobox with unique values from a column on Sheet2 that corresponds to the named ranges you are using in the lookups?

 

[wcm69]

Yes. The combobox is where I want to look up the full names (all unique) from my database spreadsheet and then have the remaining textboxes filled with the matching details for that name.

I have used the properties box row source to get the list of full names (column E) from the spreadsheet.

 

[Norie]

Are the other details, e.g. NINumber, Trade2 etc, in columns on the same worksheet?

For example if you were to pick a name from the combobox that is on row 11 on the worksheet the details for that name would also be on the same row.

 

[wcm69]

That's correct.

All the details I need to populate the userform are on 1 spreadsheet (Sheet2) as a database of information for Trades people that I'm trying to create.