Display Range of Values with MsgBox

**akynyemi** · 01-16-2013, 05:50 AM

Hi,

I am new to VBA Macros. I need help on how to display a range of cells in a Message Box.

For example, to display A1:B10 on the same line in the message box separated by "has a balance of" in the form (A1 has a balance of B1, A2 has a balance of B2) etc.

Thanks.

**jraj1106** · 01-16-2013, 05:56 AM

Hi.

Try this code:

Sub hasabalance()
    Dim rw As Long
    
    For rw = 1 To 10
        MsgBox Range("A" & rw) & " has a balance of " & Range("B" & rw)
    Next
End Sub

**akynyemi** · 01-16-2013, 06:43 AM

Hi jraj1106,

Thanks so much for your quick response.

I was able to achieve this myself, I actually want the whole range to be displayed together at the same time instead of clicking OK and the next value coming up in the next message box and so on. I want to see the whole data from A1:B10 together.

Thanks.

**Norie** · 01-16-2013, 05:59 AM

Try this.

For Each cl In Range("A1:A10")
     strMsg = strMsg & cl.Value & " has a balance of " & cl.Offset(,1).Value & ","
Next cl

MsgBox Left(strMsg, Len(strMsg)-1)

**TMS** · 01-16-2013, 07:32 AM

To build on Norie's reply:

Dim cl As Range
Dim strMsg As String

For Each cl In Range("A1:A10")
     strMsg = strMsg & cl.Value & " has a balance of " & _
     Application.WorksheetFunction.Text(cl.Offset(, 1).Value, "##0.00") & vbCr
Next cl

MsgBox Left(strMsg, Len(strMsg) - 1)

Regards, TMS

**akynyemi** · 01-16-2013, 08:08 AM

Thanks TMShucks it worked.

Thanks Guys.

**TMS** · 01-16-2013, 08:56 AM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.