How to find a string value in a column and return values in all cells from that row?

Question

I'm creating a user form that will ask for a quote number, populate the data after the quote number has been found, and update any information. The macro code I am currently using doesn't exactly work with this new user form.

I managed to get the textboxes to populate with the code below, but now I need it to actually update the cells if I change any text box values.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range
Dim Sold As String, Soldlr As Long

Set ws = Sheets("Data Entry")

With ws
    strSearch = Me.TextBox1.Value
    Set aCell = .Columns(2).Find(What:=strSearch, LookIn:=xlValues, _
                                 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        Me.TextBox1.Text = aCell.Value
        Me.TextBox2.Text = aCell.Offset(, -1).Value
        Me.TextBox3.Text = aCell.Offset(, 0).Value
        Me.TextBox4.Text = aCell.Offset(, 1).Value
        Me.TextBox5.Text = aCell.Offset(, 2).Value
        Me.TextBox6.Text = aCell.Offset(, 3).Value
        Me.TextBox7.Text = aCell.Offset(, 4).Value
        Me.TextBox8.Text = aCell.Offset(, 5).Value

    Else
        MsgBox "Quote Number " & strSearch & " Not Found. Try Again"

    End If

    Exit Sub
End With
End Sub

Private Sub CommandButton2_Click()

Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range
Dim Sold As String, Soldlr As Long

Set ws = Sheets("Data Entry")

With ws
    strSearch = Me.TextBox1.Value
    Set aCell = .Columns(2).Find(What:=strSearch, LookIn:=xlValues, _
                                 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        aCell.Offset(, -1).Value = Me.TextBox2.Text
        aCell.Offset(, 0).Value = Me.TextBox3.Text
        aCell.Offset(, 1).Value = Me.TextBox4.Text
        aCell.Offset(, 2).Value = Me.TextBox5.Text
        aCell.Offset(, 3).Value = Me.TextBox6.Text
        aCell.Offset(, 4).Value = Me.TextBox7.Text
        aCell.Offset(, 5).Value = Me.TextBox8.Text

        MsgBox "Quote Number " & strSearch & " Has Been Updated"
    End If
End With

Exit Sub
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label6_Click()

End Sub

Private Sub Label8_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Once modified, I can easily change the data in the text boxes and update the information.

Answer 1

In essence you're using Offsets so that if your number was found in B10 and you wanted C10 to go in Textbox1 you'd use

Me.Textbox1.Value = aCell.Offset(, 1).value

assuming this code lies behind the form.