Display 2 ranges in message box

[MrFlyer]

Hey need assistance with a vba. would like to have a message box show a specific cell and then a range determined by an input box.
what I'd like is display A1 then what is entered by the input box.

I was able to get a message box to show a cell.

Sub message()

Dim q As String, fnd As Range
q = InputBox("Find Person")

If q = "" Then Exit Sub

Set fnd = Range("B:B").Find(q, LookIn:=xlValues, lookat:=xlPart)
If Not fnd Is Nothing Then
Range(fnd, fnd.Offset(, -1)).Select
MsgBox (fnd.Offset(, -1))
End If

End Sub

 

[VBasic2008]

The message box will display the value of the cell (column A) to the left of the cell (column B) where the persons name was found. But both cells will be selected though. Could you explain what is wrong with that? What would you like the code to do?

 

[MrFlyer]

When I run the code I pasted. I only get the data from column A. not A & B.

 

[VBasic2008]

Here are some solutions:
All solutions will show the name and the offset value first, and then the other way around.
The first 2 will not select.
The last 4 will select.
The last 3 will not select if you out-comment the line rng.Select.

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

 

[MrFlyer]

Awesome, Thank you for the help!