Search Value in InputBOX, Look for Value, and Show Values in MSGBOX-CodePudding

I am still new to VBA. I am working on an excel sheet, and I want to look up data using InputBox and get results from the spreadsheet that I have. Here's an example:

Sheet Display:

Names	Occupation	IDs
James	Engineer	e2134
Linda	Programmer	f2123

Input Box:

"Enter IDs:"

e2134

Result:

Message Box will show:

IDs: e2134

Name: James

Occupation: Engineer

I did some code, but I can't access it from home (it's on my work computer).

I want to know what code can I use so VBA can collect the data on the same row (Name and Occupation) and display it in a MessageBox. Or there could be a better way to do that.

CodePudding user response：

You may try below code-

Sub SearchNreturn()
Dim Rng As Range
Dim strID As String

strID = InputBox("Enter ID")
Set Rng = Sheets("Sheet1").Range("C:C").Find(What:=strID, _
            After:=Cells(1, 3), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

            If Not Rng Is Nothing Then
               MsgBox "ID: " & Rng.Value & vbNewLine & _
                      "Occupation: " & Rng.Offset(0, -1) & vbNewLine & _
                      "Name: " & Rng.Offset(0, -2)
            Else
                MsgBox "Nothing found."
            End If
End Sub