VBA- runtime error 1004 - Vlookup

[Raymondc190466]

Hi Everybody,

I have issues with the population of the textbox7, and get a runtime error once data is written to a specific worksheet.
Any ideas how to solve this?

Private Sub UserForm_Initialize()
    
    With Worksheets("Supplier")
        ComboBox1.List = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    With Worksheets("Combobox")
        ComboBox2.List = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row).Value
    With Worksheets("Combobox")
        ComboBox3.List = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    With Worksheets("Combobox")
        ComboBox4.List = .Range("E1:E" & .Range("E" & .Rows.Count).End(xlUp).Row).Value
    End With
    End With
    End With
    End With

Me.TextBox2.Value = "Benelux"
UserForm1.BackColor = RGB(0, 0, 255)
UserForm3.BackColor = RGB(0, 0, 255)
UserForm4.BackColor = RGB(0, 0, 255)
      
End Sub

Private Sub ComboBox1_Change()

Me.TextBox7.Text = Application.VLookup(ComboBox1.Value, Worksheets("Supplier").Range("A2:B69"), 2, False)
End Sub

 

[RoryA]

I can't see how you would get a 1004 error on that line. A type mismatch, yes, but not a 1004.

 

[Raymondc190466]

I can't see how you would get a 1004 error on that line. A type mismatch, yes, but not a 1004.

Hi Rory,

Updated the code, but still got an error:

Private Sub UserForm_Initialize()
    Dim xRg As Range
    
    Set xRg = Worksheets("Supplier").Range("A2:B65")
    Me.ComboBox1.List = xRg.Columns(1).Value
    
    Private Sub ComboBox1_Change()

Me.TextBox7.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)

End Sub

Example of data:

 

[RoryA]

That can’t be the actual code as it wouldn’t run at all.

The error if it did run would mean the lookup value isn’t being found. Is a value actually selected at the time?

 

[Alex Blakenburg]

@Raymondc190466, You seem to have introduced more issues with you latest posting. Try this:

Private Sub ComboBox1_Change()
    Dim xRg As Range
   
    With Worksheets("Supplier")
        Set xRg = .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
   
    If IsError(Application.VLookup(Me.ComboBox1.Value, xRg, 2, False)) Then
        MsgBox "Unable to find " & Me.ComboBox1.Value
    Else
        Me.TextBox7.Text = Application.VLookup(Me.ComboBox1.Value, xRg, 2, False)
    End If
   
End Sub

 

[Raymondc190466]

@Raymondc190466, You seem to have introduced more issues with you latest posting. Try this:

Private Sub ComboBox1_Change()
    Dim xRg As Range
  
    With Worksheets("Supplier")
        Set xRg = .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
  
    If IsError(Application.VLookup(Me.ComboBox1.Value, xRg, 2, False)) Then
        MsgBox "Unable to find " & Me.ComboBox1.Value
    Else
        Me.TextBox7.Text = Application.VLookup(Me.ComboBox1.Value, xRg, 2, False)
    End If
  
End Sub

Hi Alex,

Code is working, thank you very much!

To all other helpers ,Sorry if I caused any uncertainties.
Everybody thanks for the help and input!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.