Vlookup through 3 sheets from one lookup value result in all textbox in userform vba

You'll have to line up the textbox numbers with the column numbers.

Private Sub CommandButton1_Click()
Dim Answer As Variant
Dim A As Long
Dim tmp
    Answer = TextBox1.Value
    If Answer <> "" Then
        For A = 2 To 37
        '=VLOOKUP(B2, B2:AQ165000,2,0)
        tmp = Application.VLookup(Val(Answer), Worksheets("Dispatchcalls").Range("B1:AQ16"), A - 1, False)
            Me.Controls("TextBox" & A).Value = tmp
         'so on and so forth for several other combo- and textboxes
        Next
    End If
End Sub

any experts would help on this task???

Now that I've reread the thread, I'm confused.

Do you want to lookup in the userform and edit the values?

Or do you want the Report generated?

Or do you want both?

I changed the textbox1 to a combobox1. I then create a unique list of the SO#'s and put them into the combobox. It works really well.

Option Explicit
Private Sub UserForm_Initialize()
Dim WS As Worksheet
Dim Unique As New Collection
Dim A As Long
Dim LastRow As Long

Set WS = Worksheets("DispatchCalls")

With WS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    For A = 2 To LastRow
        On Error Resume Next
        Unique.Add CStr(.Range("B" & A)), CStr(A)
        On Error GoTo 0
    Next
End With

    For A = 1 To Unique.Count
        Me.ComboBox1.AddItem Unique(A)
    Next
End Sub

Private Sub combobox1_change()
Dim WS As Worksheet
Dim LastRow As Long
Dim C As Range
Dim Answer As Variant
Dim A As Long
Dim Ctrl As Control

'Clear all textboxes
For Each Ctrl In Me.Controls
    Select Case TypeName(Ctrl)
    Case "TextBox"
        Ctrl.Value = ""
    End Select
Next

    Answer = Me.ComboBox1
    If Answer <> "" Then
        'Dispatch
        Set WS = Worksheets("DispatchCalls")
        
        With WS
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            With .Range("B2:B" & LastRow)
                Set C = .Find(Answer, , xlValues)
                If Not C Is Nothing Then
                    'Header
                    Me.TextBox2 = C.Offset(, -1)
                    Me.TextBox3 = C.Offset(, Range("C1").Column - 2)
                    Me.TextBox4 = C.Offset(, Range("D1").Column - 2)
                    Me.TextBox5 = C.Offset(, Range("E1").Column - 2)
                    Me.TextBox6 = C.Offset(, Range("G1").Column - 2)
                    Me.TextBox7 = C.Offset(, Range("H1").Column - 2)
                    Me.TextBox8 = C.Offset(, Range("J1").Column - 2)
                    Me.TextBox9 = C.Offset(, Range("K1").Column - 2)
                    Me.TextBox10 = C.Offset(, Range("N1").Column - 2)
                    Me.TextBox11 = C.Offset(, Range("U1").Column - 2)
                    
                    'Dispatch Calls
                    Me.TextBox12 = C.Offset(, Range("Y1").Column - 2)
                    Me.TextBox13 = C.Offset(, Range("Z1").Column - 2)
                    Me.TextBox14 = C.Offset(, Range("AA1").Column - 2)
                    Me.TextBox15 = C.Offset(, Range("AB1").Column - 2)
                    Me.TextBox16 = C.Offset(, Range("AC1").Column - 2)
                    Me.TextBox17 = C.Offset(, Range("Y1").Column - 2)
                    Me.TextBox18 = C.Offset(, Range("AD1").Column - 2)
                    Me.TextBox19 = C.Offset(, Range("AE1").Column - 2)
                    Me.TextBox20 = C.Offset(, Range("AF1").Column - 2)
                    Me.TextBox21 = C.Offset(, Range("AG1").Column - 2)
                    Me.TextBox22 = C.Offset(, Range("AI1").Column - 2)
                    Me.TextBox23 = C.Offset(, Range("AJ1").Column - 2)
                    Me.TextBox24 = C.Offset(, Range("AK1").Column - 2)
                End If
            End With
        End With
            
        Set WS = Worksheets("ClosedCalls")
        
        With WS
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            With .Range("B2:B" & LastRow)
                Set C = .Find(Answer, , xlValues)
                If Not C Is Nothing Then
                    'Closed Calls
                    Me.TextBox25 = C.Offset(, Range("AL1").Column - 2)
                    Me.TextBox26 = C.Offset(, Range("AM1").Column - 2)
                    Me.TextBox27 = C.Offset(, Range("AN1").Column - 2)
                    Me.TextBox28 = C.Offset(, Range("AO1").Column - 2)
                    Me.TextBox29 = C.Offset(, Range("AP1").Column - 2)
                    Me.TextBox30 = C.Offset(, Range("AQ1").Column - 2)
                    Me.TextBox31 = C.Offset(, Range("AR1").Column - 2)
                    Me.TextBox32 = C.Offset(, Range("AS1").Column - 2)
                    Me.TextBox33 = C.Offset(, Range("AT1").Column - 2)
                    Me.TextBox34 = C.Offset(, Range("AV1").Column - 2)
                    Me.TextBox35 = C.Offset(, Range("AW1").Column - 2)
                    
                End If
            End With
        End With
    
        Set WS = Worksheets("CancelCalls")
        
        With WS
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            With .Range("B2:B" & LastRow)
                Set C = .Find(Answer, , xlValues)
                If Not C Is Nothing Then
                    'Cancel Calls
                    Me.TextBox36 = C.Offset(, Range("AI1").Column - 2)
                    Me.TextBox37 = C.Offset(, Range("AK1").Column - 2)
                End If
            End With
        End With
    End If
End Sub

I just learned something new. If the column width isn't wide enough, Excel will show the value as scientific notation and Range Find WILL NOT FIND the actual value.

See the attached.

@ Tinbendr,

Good work!

Regards.