How to display a search result in a UserForm's TextBox by matching the search results from another TextBox?

Question

I am trying to find the code for a this UserForm with 2 TextBoxes:

I have a separate sheet with the data:

I need to search with REF ID (in column 1) and it should display only the column 4 (notes) in the textbox2 below, with WordWrap and MultiLine properties set to True. Can anybody help me with this?

I tried this:

Private Sub CommandButton1_Click()
Dim Search As String
Dim FoundCell As Range, SearchRange As Range
Dim ws As Worksheet
Set ws = Worksheets("Database")
' search sheet change name as required
Set SearchRange = ws.Range("A1", ws.Range("J65536").End(xlUp))

Search = Me.jobid.Text

If Len(Search) = 0 Then Exit Sub

Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then
    Me.resultid.Value = FoundCell.Offset(0, 4).Value
Else
    MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
End If

End Sub

I get error in

Me.resultid.Value = FoundCell.Offset(0, 4).Value

What am I doing wrong?

Answer 1

The main thing is probably your offset. Change it from 4 to 3:

Me.resultid.Value = FoundCell.Offset(0, 3).Value

Another not correct thing probably is your SearchRange. Now it spans from column A to column J. As I understand from the question you want to only look at column A (REF ID). So change SearchRange to this:

Set SearchRange = ws.Range("A1", ws.Range("A65536").End(xlUp))

After making these changes I was able to search for "H001" which is in cell A2 and write the value from cell D2 into the other TextBox: