Put a dynamic array into a string and then send results to msgbox

[caerdydd]

Hello
I am trying to convert a dynamic array to a string in excel vba and then send the results to the message box. From my code below, i have the dynamic array and the CStr function for the string but cannot work out how to get all my results from the array out into one message box only. Basically the code loops through a list and pulls out data that has a time later than the present time, those it pulls out get stored in the array and then all sent out to one message box.
The problem i have is with this line "mystr = CStr(myid(x))" as it only gives me the last time from the array when i want them all. How can i get around this?
Thanks very much for the help


Sub doalert(sTime)
Dim mychk
Dim i As Integer
Dim myarray
Dim lUpdate, uDate
Dim myid
Dim mycell As Excel.Range

Set mycell = Sheet1.Range("B7")
j = mycell.CurrentRegion.Rows.Count

Do Until Sheet1.Cells.Range("D7").Offset(i, 0).Value = ""

mychk = Format(Sheet1.Cells.Range("D7").Offset(i, 0).Value, "hh:mm:ss")
uDate = Sheet1.Cells.Range("D7").Offset(i, -1).Value
lUpdate = Sheet1.Cells.Range("D7").Offset(i, 0).Value
uTime = Format(sTime, "hh:mm:ss")

If mychk < uTime Then
If Sheet1.Cells.Range("D7").Offset(i, 1).Value = "Y" Then

ReDim Preserve myid(x)
myid(x) = mycell.Offset(i, 0)
x = x + 1

End If
End If

i = i + 1
Loop

mystr = CStr(myid(x))

MsgBox mystr

End Sub

 

[vbSun]

Sub doalert(sTime)
Dim mychk
Dim i As Integer
Dim myarray
Dim lUpdate, uDate
Dim myid
Dim mycell As Excel.Range
Dim strTmp As String

Set mycell = Sheet1.Range("B7")
j = mycell.CurrentRegion.Rows.Count
strTmp =""
Do Until Sheet1.Cells.Range("D7").Offset(i, 0).Value = ""

mychk = Format(Sheet1.Cells.Range("D7").Offset(i, 0).Value, "hh:mm:ss")
uDate = Sheet1.Cells.Range("D7").Offset(i, -1).Value
lUpdate = Sheet1.Cells.Range("D7").Offset(i, 0).Value
uTime = Format(sTime, "hh:mm:ss")

If mychk < uTime Then
If Sheet1.Cells.Range("D7").Offset(i, 1).Value = "Y" Then

ReDim Preserve myid(x)
myid(x) = mycell.Offset(i, 0)
strTmp = strTmp & myid(x)
x = x + 1

End If
End If

i = i + 1
Loop

' mystr = CStr(myid(x))
MsgBox strTmp
' MsgBox mystr

End Sub

------------------------------------------
The faulty interface lies between the chair and the keyboard.