VBA to lookup a combo box value in a different worksheet

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
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.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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.
Code:
	' 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

Well-known Member
Joined
Jan 22, 2009
Messages
663
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,613
Office Version
2007
Platform
Windows
Try this

Code:
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

Well-known Member
Joined
Jan 22, 2009
Messages
663
That's exactly what I was looking for. Thanks so much!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,085,089
Messages
5,381,649
Members
401,747
Latest member
Jtvan

Some videos you may like

This Week's Hot Topics

Top