Display 2 ranges in message box

MrFlyer

New Member
Joined
Aug 15, 2018
Messages
5
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Aug 15, 2018
Messages
5
When I run the code I pasted. I only get the data from column A. not A & B.
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,957
Messages
5,575,219
Members
412,651
Latest member
caitlincole
Top