Displaying a Range in a Message Box

[FlashNZ]

Hi, I found some code in a forum and it is working ok but I want to display the numbers in one column, they would ideally be all in a column where the two 12's are to the right in the pic.

Here is the code I am using:

Sub DisplayUsage()

Worksheets("All Sales").Activate

MsgBox Join(Evaluate("TRANSPOSE(AA1:AA12 & """ & vbTab & """ & AC1:AC12)"), vbNewLine)

End Sub

 

[footoo]

Aligning Multiple Columns in Msgbox

I know I can probably do this with a userform but don't really want to mess around with recoding at this point. I have a routine that eventually outputs something like the msgbox displayed below. The only problem is my tabulated spacing gets messed up when one of my words is too long (as...

www.mrexcel.com

 

[rlv01]

MsgBox is not well suited to displaying multi-column data so any solutions tend to look a bit kludgy. So for example, if the col AA data is always a month, this might work

Sub DisplayUsage()
    Dim S As String, Ext As String, I As Long, VA
    Worksheets("All Sales").Activate
    
    VA = Split(Join(Evaluate("TRANSPOSE(AA1:AA12 & """ & vbTab & """ & AC1:AC12)"), vbTab), vbTab)
    For I = 0 To UBound(VA)
        Select Case I Mod 2
            Case 0
                Select Case UCase(VA(I))
                    Case "SEPTEMBER", "NOVEMBER", "DECEMBER"
                        Ext = vbTab
                    Case Else
                        Ext = vbTab & vbTab
                End Select
            Case 1
                Ext = vbCr
        End Select
        S = S & VA(I) & Ext
    Next I
    MsgBox S
End Sub

But if you come back and say that you were just using months as an example and it could be anything, then it probably won't. A better solution would be to create your own userform and use that instead of msgbox.

 

[FlashNZ]

Thanks, works perfectly and yes only using month.

 

[footoo]

Another way :

Sub DisplayUsage()
Dim a: a = [AA1:AA12]
Dim c: c = [AC1:AC12]
Dim i, str$
For i = LBound(a) To UBound(a)
    Select Case Left(a(i, 1), 1)
        Case "S", "N", "D": str = str & a(i, 1) & vbTab & c(i, 1) & vbCr
        Case Else: str = str & a(i, 1) & vbTab & vbTab & c(i, 1) & vbCr
    End Select
Next
MsgBox str
End Sub

 

[rlv01]

Case "S", "N", "D"

Nice.