Display Array in MsgBox

[Jared_Jones_23]

Hello, I have an array of data that is calculated in a macro that is named myarray. The length of the array can vary depending on the data. I was wondering if there is anyway to display each element of the array in a message box.
Thank you,
Jared

 

[bertie]

Hi and welcome to the board.

You can loop through the array and build up a text string for output to the message box.See the example below.

[color=darkblue]Sub[/color] test()
  [color=darkblue]Dim[/color] myArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
  [color=darkblue]Dim[/color] txt [color=darkblue]As[/color] [color=darkblue]String[/color]
  [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
  
  myArray = Array("Line1", "Line2", "Line3", "Line4")
  
  [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](myArray) [color=darkblue]To[/color] [color=darkblue]UBound[/color](myArray)
    txt = txt & myArray(i) & vbCrLf
  [color=darkblue]Next[/color] i
  
  MsgBox txt
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Jared_Jones_23]

Thank you for the quick reply. It seems to still not be working, i may be defining the array wrong. Here is my code, if you could give me any suggestions on defining the array i would appreciate it.
Thank you

Dim lastRow, lastCol As Integer
Dim myarray() As Variant
Dim txt As String
Dim c As Long
With ActiveSheet
lastRow = .Cells(.Rows.count, "D").End(xlUp).Row
lastCol = .Cells(20, .Columns.count).End(xlToLeft).Column
End With

count = 0
count2 = 0
For i = 20 To lastRow
If Cells(i, 1).Interior.ColorIndex = 4 Then
myarray = Array(Rows(i))
count2 = count2 + 1
End If
For j = 1 To 26
If Cells(i, j).Interior.ColorIndex = 28 Then
count = count + 1
End If
Next j
Next i
If count > 0 Then
MsgBox ("Please Fill In Blue Highlighted Cells"), vbOKOnly
End If
For c = LBound(myarray) To UBound(myarray)
txt = txt & myarray(c) & vbCrLf
Next c
MsgBox txt
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[bertie]

Its the way you are populating the array.

Each time you add something to the array within the loop you are increasing the size of the array.
In VBA code we have to REDIM(ention) the array with each addition.
We also need to PRESERVE existing values.

    [color=darkblue]If[/color] Cells(i, 1).Interior.ColorIndex = 4 [color=darkblue]Then[/color]

      count2 = count2 + 1
      [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] myarray(1 [color=darkblue]To[/color] count2)
      myarray(count2) = "Row: " & i

    [color=darkblue]End[/color] [color=darkblue]If[/color]

ps I have assumed you are adding the row number to the array.

 

[Jared_Jones_23]

Thank you for your help. I have it working now
Cheers