find names value via combobox

[hsolanki]

Morning everyone

I have got the code which perfectly working. combobox1 finds the staff number and then populates names in Combobox 1 & 5 however i would like to change this from a staff number to names find

below is the code whereby it finds it using the staff number. the staff number is in column A & names in column B

Private Sub ComboBox1_Change()

    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), ThisWorkbook.Sheets("Monthly Summary").Range("A:B"), 2, 0)
        Me.TextBox6.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), ThisWorkbook.Sheets("Monthly Summary").Range("A:C"), 3, 0)
        
    Else
        Me.TextBox1.Value = ""
    End If

End Sub

 

[Legacy 143009]

You are going to select name and bring..what...?

 

[hsolanki]

Hi sorry, when i select the names i want to bring back staff numbers in textbox1.

basically, i want to reverse.

 

[shinigamilight]

populates names in Combobox 1 & 5

In the code it says it populates in the textbox

 

[hsolanki]

Hi Yes at the moment but if i can change the names into the column box instead of the number then the textbox will also need to show the names alone

 

[Legacy 143009]

Private Sub ComboBox1_Change()
    With ThisWorkbook.Sheets("Monthly Summary")
    If Me.ComboBox1.Value <> "" Then
        Me.TextBox6.Value = .Cells(WorksheetFunction.Match(Me.ComboBox1.Value, .Range("B:B"), 0), 1).Value
    Else
        Me.TextBox1.Value = ""
    End If
    End With
End Sub

 

[Legacy 143009]

Change

Me.TextBox6.Value

To

Me.TextBox1.Value

 

[hsolanki]

Hi Flashbond, Thank you so much, it wokred however i have forgotten that in the textbox6 i also needed to find the number values which was in textbox6, therefore, i amended the code and added textbox6 line . it was finding the names and the holiday entitlement which was in textbox6 showing however i get an error now

Private Sub ComboBox1_Change()
   
        With ThisWorkbook.Sheets("Monthly Summary")
    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = .Cells(WorksheetFunction.Match(Me.ComboBox1.Value, .Range("B:B"), 0), 1).Value
        Me.TextBox6.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), .Range("C:C"), 0, 1).Value
        
    Else
        Me.TextBox1.Value = ""
    End If
    End With

End Sub

This is the line i get error:

Me.TextBox6.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), .Range("C:C"), 0, 1).Value

 

[Legacy 143009]

Me.TextBox6.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), .Range("C:C"), 0, 1).Value

There are so mant wrongs with this line. I don't know where to start..

1.First you said

select the names

You can not cast names into Long integers like CLng(Me.ComboBox1.Value). Of it is a name, then what do you expect the result to be?

2. I am not sure you can vlookup to single column. Generally, you lookup more than 1 column like .Range("B:C"). If you match the calue on column B, then tou can return the 2nd column value which is C.

3. Which brings us to 3rd problem. There is nothing as 0 column number. The column number you should return must be greater than 0.

4. Why are you returning close match 1? For an exact match, the last argument must be 0.

5. Last problem but not the least, you can not return .Value from a Vlookup function. The function itself already returns a value. Delete it.

After evaluating all these, I suspect you are experienced in using Vlookup function. Tell me what do you have in columns A, B and C. What do you have in combobox? And what do you want to return in Textbox6?

 

[hsolanki]

Morning Flashbond, apologies for the mess, however, i am not great at coding and just making my away in learning but I do really appreciate for your guidance.

i have attached a snapshot of what i am trying to achieve and for easier understanding