VBA to lookup a combo box value in a different worksheet

[Zimmerman]

ok so I have been racking my head against the wall to try and figure this out. I have a userform that when the user clicks the ok button. I need to get this to perform a vlookup the value from a combobox in sheet "Employee Data" in range B4:B49. It needs to return the value in column 3. But if 1 of the 4 Option buttons has been clicked then it needs to return the value specified. Right now I have it entering the data in the last row available. Attached is the code that works. Everything except for this lookup function. Honestly I'm not sure how to write this part.

Private Sub CommandButton2_Click()
  Dim sday As String, f As Range, fa As Range, fb As Range, fc As Range, fd As Range, fe As Range, Cl As Range, rng As Range, lr As Long, sh As Worksheet, Dic As Object
  ActiveSheet.Unprotect Password:=""
  Set sh = ActiveSheet
  Set wsLookup = ("Employee Data")
  Set f = sh.Range("A:A").Find(cboEmployee, , xlValues, xlWhole)
  Set fa = sh.Range("A:A").Find(cboEmployee2, , xlValues, xlWhole)
  Set fb = sh.Range("A:A").Find(cboEmployee3, , xlValues, xlWhole)
  Set fc = sh.Range("A:A").Find(cboEmployee4, , xlValues, xlWhole)
  Set fd = sh.Range("A:A").Find(cboEmployee5, , xlValues, xlWhole)
  Set fe = sh.Range("A:A").Find(cboEmployee6, , xlValues, xlWhole)
  
  
  If Not f Is Nothing Then
    lr = f.Row
    Do While sh.Cells(lr, "C") <> ""
      lr = lr + 1
    Loop
    sh.Cells(lr, "C").Value = TextBox1.Value
    sh.Cells(lr, "D").Value = TextBox2.Value
    sh.Cells(lr, "H").Value = TextBox3.Value
    sh.Cells(lr, "I").Value = TextBox4.Value
    sh.Cells(lr, "B").Value = WorksheetFunction.VLookup(cboEmployee.Value(Sheets("Employee Data").Range("B4:B49"), 3,False)
    'Inputs different department # if changed
    If OptionButton1.Value = True Then
    sh.Cells(lr, "B").Value = ("300")
    End If
    If OptionButton2.Value = True Then
    sh.Cells(lr, "B").Value = ("325")
    End If
    If OptionButton3.Value = True Then
    sh.Cells(lr, "B").Value = ("350")
    End If
    If OptionButton4.Value = True Then
    sh.Cells(lr, "B").Value = ("360")
    End If
  End If

Thanks in advance
Excel 2016

 

[Norie]

This code will try and find the value selected in cboEmployee in column B of 'Employee Data' and if it's found will put the value from column C in the corresponding row in the next empty row of column B.

If the value selected isn't found then the value put in the next empty row of column B will depend on which option button is selected.

	' find row value selected in cboEmployee is in on 'Employee Data'
	Res = Appliction.Match(cboEmployee.Value,Sheets("Employee Data").Range("B:B"), 0)
	
	If Not IsError(Res) Then
        sh.Cells(lr, "B").Value = Sheets("Employee Data").Cells(Res, "C").Value
	Else
	
	    'Inputs different department # if changed
	    If OptionButton1.Value = True Then
	        sh.Cells(lr, "B").Value = "300"
	    End If
	    If OptionButton2.Value = True Then
	        sh.Cells(lr, "B").Value = "325"
	    End If
	    If OptionButton3.Value = True Then
	        sh.Cells(lr, "B").Value = "350"
	    End If
	    If OptionButton4.Value = True Then
	        sh.Cells(lr, "B").Value = "360"
	    End If
	End If

 

[Zimmerman]

Thanks but it gave me a Run-time error of 424 Object required. There will always be a match though. The cboEmployee is a combobox that has a list of the employees from the sheet Employee Data. All I'm trying to do is pull their Employee department number over and attach it to them. But some times they work in other departments so we need to transfer them to whichever one they worked at.

 

[DanteAmor]

Try this

Private Sub CommandButton2_Click()
  Dim sday As String, f As Range, [COLOR=#0000ff]f2 As Range[/COLOR]
  Dim fa As Range, fb As Range, fc As Range, fd As Range, fe As Range, Cl As Range, rng As Range, lr As Long, sh As Worksheet, Dic As Object
  ActiveSheet.Unprotect Password:=""
  Set sh = ActiveSheet
  '[COLOR=#ff0000]Set wsLookup = ("Employee Data") [/COLOR] [COLOR=#FF0000]'This is incorrect![/COLOR]
  Set f = sh.Range("A:A").Find(cboEmployee, , xlValues, xlWhole)
'  Set fa = sh.Range("A:A").Find(cboEmployee2, , xlValues, xlWhole)
'  Set fb = sh.Range("A:A").Find(cboEmployee3, , xlValues, xlWhole)
'  Set fc = sh.Range("A:A").Find(cboEmployee4, , xlValues, xlWhole)
'  Set fd = sh.Range("A:A").Find(cboEmployee5, , xlValues, xlWhole)
'  Set fe = sh.Range("A:A").Find(cboEmployee6, , xlValues, xlWhole)
  If Not f Is Nothing Then
    lr = f.Row
    Do While sh.Cells(lr, "C") <> ""
      lr = lr + 1
    Loop
    sh.Cells(lr, "C").Value = TextBox1.Value
    sh.Cells(lr, "D").Value = TextBox2.Value
    sh.Cells(lr, "H").Value = TextBox3.Value
    sh.Cells(lr, "I").Value = TextBox4.Value
    'Inputs different department # if changed
    If OptionButton1.Value = True Then
      sh.Cells(lr, "B").Value = ("300")
    [COLOR=#0000ff]ElseIf [/COLOR]OptionButton2.Value = True Then
      sh.Cells(lr, "B").Value = ("325")
    [COLOR=#0000ff]ElseIf [/COLOR]OptionButton3.Value = True Then
      sh.Cells(lr, "B").Value = ("350")
    [COLOR=#0000ff]ElseIf [/COLOR]OptionButton4.Value = True Then
      sh.Cells(lr, "B").Value = ("360")
[COLOR=#0000ff]    Else[/COLOR]
[COLOR=#0000ff]      Set f2 = Sheets("Employee Data").Range("B4:B49").Find(cboEmployee, , xlValues, xlWhole)[/COLOR]
[COLOR=#0000ff]      If Not f2 Is Nothing Then[/COLOR]
[COLOR=#0000ff]        sh.Cells(lr, "B").Value = f2.Offset(, 1)[/COLOR]
[COLOR=#0000ff]      End If[/COLOR]
    End If
  End If
End Sub

 

[Zimmerman]

That's exactly what I was looking for. Thanks so much!

 

[Norie]

If the employee combobox is populated from the list of employees then you don't need Find/Application.Match, the ListIndex of the combobox will correspond directly to the row of the selected employee.

 

[DanteAmor]

That's exactly what I was looking for. Thanks so much!

I'm glad to help you. Thanks for the feedback.