Userform multiple vlookup based on combobox

[trev1234]

Hi,
I have a userform with combobox6 on it which has two options Full and Part based on what is selected I want to update a textbox using vlookup the issue I am having is that the vlookup range is different dependent on the selection from the combobox, I have tried the below but I cannot get it to work. any help would be appreciated.

Combobox3 if the vlookup value this is also on the userform

Private Sub ComboBox6_Change()


If ComboBox6 = "Full" Then
TextBox2.Value = Application.VLookup(Me.ComboBox3.Value, Sheets("Absence Totals").Range("a:g"), 7, 0)
ElseIf ComboBox6 = "part" Then
TextBox2.Value = Application.VLookup(Me.ComboBox3.Value, Sheets("Absence Totals").Range("a:h"), 8, 0)


Else
End If


End Sub

 

[DanteAmor]

Try this And comment what message appears.

Private Sub ComboBox6_Change()
  Dim r As Range, f As Range
  
  TextBox2.Value = ""
  
  If comboBox3.Value = "" Then
    MsgBox "Fill combo3"
    Exit Sub
  End If
  
  Set r = Sheets("Absence Totals").Range("A:A")
  Set f = r.Find(comboBox3.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    If LCase(ComboBox6.Value) = LCase("Full") Then
      TextBox2.Value = f.Offset(0, 6)
    ElseIf LCase(ComboBox6.Value) = LCase("Part") Then
      TextBox2.Value = f.Offset(0, 7)
    End If
  Else
    MsgBox "Data does not exist"
  End If
End Sub

 

[trev1234]

Try this And comment what message appears.

Private Sub ComboBox6_Change()
  Dim r As Range, f As Range
 
  TextBox2.Value = ""
 
  If comboBox3.Value = "" Then
    MsgBox "Fill combo3"
    Exit Sub
  End If
 
  Set r = Sheets("Absence Totals").Range("A:A")
  Set f = r.Find(comboBox3.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    If LCase(ComboBox6.Value) = LCase("Full") Then
      TextBox2.Value = f.Offset(0, 6)
    ElseIf LCase(ComboBox6.Value) = LCase("Part") Then
      TextBox2.Value = f.Offset(0, 7)
    End If
  Else
    MsgBox "Data does not exist"
  End If
End Sub

That is exactly what I am looking for, thank you DanteAmor. Once again someone in this forum has saved me hours of headaches

 

[DanteAmor]

Im glad to help you, thanks for the feedback.