Option Explicit
' If you don't need to select:
Sub message1()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
MsgBox q & " " & fnd.Offset(, -1).Value
' Switch if you want the name first.
MsgBox fnd.Offset(, -1).Value & " " & q
End If
End Sub
' If you don't need to select using with:
Sub message2()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
With Range(fnd.Offset(, -1), fnd)
MsgBox q & " " & .Cells(1).Value
' Switch if you want the name first.
MsgBox .Cells(1).Value & " " & q
End With
End If
End Sub
' If you need to select:
Sub message3()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
Range(fnd.Offset(, -1), fnd).Select
With Selection
MsgBox q & " " & .Cells(1).Value
' Switch if you want the name first.
MsgBox .Cells(1).Value & " " & q
End With
End If
End Sub
' If you wanna use a variable:
Sub message4()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
Dim rng As Range
Set rng = Range(fnd.Offset(, -1), fnd)
rng.Select ' if you need to select
With rng
MsgBox q & " " & .Cells(1).Value
' Switch if you want the name first.
MsgBox .Cells(1).Value & " " & q
End With
End If
End Sub
' If you wanna use a variable, without 'With':
Sub message5()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
Dim rng As Range
Set rng = Range(fnd.Offset(, -1), fnd)
rng.Select ' if you need to select
MsgBox rng.Cells(1).Value & " " & q
' Switch if you want the name first.
MsgBox q & " " & rng.Cells(1).Value
End If
End Sub
' If you wanna use a variable, without `With` and shortened:
Sub message6()
Dim q As String, fnd As Range
q = InputBox("Find Person")
If q = "" Then Exit Sub
Set fnd = Range("B:B").Find(What:=q, LookIn:=xlValues, LookAt:=xlPart)
If Not fnd Is Nothing Then
Dim rng As Range
Set rng = Range(fnd.Offset(, -1), fnd)
rng.Select ' if you need to select
MsgBox rng(1).Value & " " & q
' Switch if you want the name first.
MsgBox q & " " & rng(1).Value
End If
End Sub