userform vlookup without using application.vlookup

Question

I am currently trying to create a userform that would call the matching values of column B,C,D with column A value. In order to show value of column B, C and D into textbox2, textbox3 and textbox4 then I have to put the value of column A to textbox1. My data in my sheet is indefinite so as much as possible I don't want to use application.vlookup. I've used a code for this matter that my colleague provided me which is actually working on some of the userforms that I created. However, as I use it now it just won't work.

Private Sub Textbox1_AfterUpdate()

If Textbox1.Value = "" Then

Textbox2 = ""
Textbox3 = ""
Textbox4 = ""
Exit Sub

Else

x = 2
Do Until Sheet2.Cells(x, "A") = Textbox1.Value
If Sheet2.Cells(x, "C").Value = "" Then
Textbox2 = ""
Textbox3 = ""
Textbox4 = ""
Exit Sub
End If

x = x + 1
Loop

Textbox2 = Sheet2.Cells(x, "B")
Textbox3 = Sheet2.Cells(x, "C")
Textbox4 = Sheet2.Cells(x, "D")
End If

End Sub

I hope you can look into this, point out what is wrong and suggest any corrections.

Thank you!

Answer 1

You objections to VLookupare not valid. There is no need to create a name for the column of my lookup nor to redo range of the name as the lookup values

Just do the search on the whole column.

That said, Application.Match is actually more useful in this case

Your code, refactored

Private Sub Textbox1_AfterUpdate()
    Dim x As Variant
    If Textbox1.Value <> vbNullString Then
        With Sheet2
            x = Application.Match(Textbox1.Value, .Columns(1), 0)
            If Not IsError(x) Then
                If .Cells(x, "C").Value <> vbNullString and x >= 2 Then
                    Textbox2 = .Cells(x, "B")
                    Textbox3 = .Cells(x, "C")
                    Textbox4 = .Cells(x, "D")
                    Exit Sub
                End If
            End If
        End With
    End If
    Textbox2 = vbNullString
    Textbox3 = vbNullString
    Textbox4 = vbNullString
End Sub

before adding data to TextBox1

after adding data to TextBox1