VLOOKUP with VBA

[asyamonique]

Good Day,
On my worksheet column A:B has huge data all the way down.
Column A has ID numbers of the clients, column B has the names and surnames.Could you help me with a code that on my userform I like to put two textboxes,
TextBox2 will show the clients name when the TextBox1 has been updated with new entry.
Many Thanks.

 

[BigBeachBananas]

How about this?

Private Sub TextBox1_Change()
    Dim ws As Worksheet
    Dim clientID As String
    Dim clientName As String
    Dim lastRow As Long
    Dim i As Long

  
    Set ws = ThisWorkbook.Sheets("Sheet1") '<~~ Replace "Sheet1" with your sheet name

    clientID = TextBox1.Value

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastRow
        If ws.Cells(i, 1).Value = clientID Then
            clientName = ws.Cells(i, 2).Value
            Exit For
        End If
    Next i

    TextBox2.Value = clientName
End Sub

 

[asyamonique]

Perfect
Thank you.

 

[asyamonique]

How about this?

Private Sub TextBox1_Change()
    Dim ws As Worksheet
    Dim clientID As String
    Dim clientName As String
    Dim lastRow As Long
    Dim i As Long

 
    Set ws = ThisWorkbook.Sheets("Sheet1") '<~~ Replace "Sheet1" with your sheet name

    clientID = TextBox1.Value

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastRow
        If ws.Cells(i, 1).Value = clientID Then
            clientName = ws.Cells(i, 2).Value
            Exit For
        End If
    Next i

    TextBox2.Value = clientName
End Sub

Hello again,
If i place another textbox for the column C, is there any way to alter that given code?

TextBox3.Value = ?


Many Thanks

 

[asyamonique]

I think I've done it

Dim ws As Worksheet
    Dim clientID As String
    Dim clientName As String
    Dim clientName2 As String

    Dim lastRow As Long
    Dim i As Long

  
    Set ws = ThisWorkbook.Sheets("PLAYERS") '<~~ Replace "Sheet1" with your sheet name

    clientID = TextBox1.Value

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastRow
        If ws.Cells(i, 1).Value = clientID Then
            clientName = ws.Cells(i, 2).Value
            clientName2 = ws.Cells(i, 3).Value

            Exit For
        End If
    Next i

    TextBox2.Value = clientName
    TextBox3.Value = clientName2