Hi folks, I've searched up and down these forums, maybe I'm not usin
the right keywords, but I can't find an answer. I have a dynami
array, TNum(), that gets filled with anywhere from 1 to 6 number
depending on the item name entered. The problem is, I want to make
message box that will output whatever numbers are in the array
separated by commas. I tried a fixed array, but when the array ha
less than 6 numbers, it'd return an out of bounds error, as I expected


I know my code is wrong (aside from the fact that it doesn't do what
want), but I feel I'm on the right track. I think all I need is a wa
to create the entire string first and then put it into the msgbox.
can't figure it out though. Pleeeaaase help meeee...:(


Code
-------------------
Iput = InputBox("Please enter item:")
Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows)
FoundRow = FoundCell.Row

For i = 2 To 7

If Worksheets(1).Cells(FoundRow, i).Value < "" Then
j = j + 1

ReDim Preserve TNum(1 To j)
TNum(j) = Worksheets(1).Cells(FoundRow, i).Value

End If

Next i

For l = 1 To UBound(TNum())
Term2 = Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##"
-------------------

--
carg
-----------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...fo&userid=1527
View this thread: http://www.excelforum.com/showthread.php?threadid=39075

Try something like

Dim strNums As String

strNums = CStr(TNum(1))

For i = 2 to j ' assuming j is the number of elements in the array

strNums = strNums & ", " & CStr(TNum(i))

Next i

MsgBox(strNums)

'CStr converts the number to a string


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=390757

IT WORKS! IT WORKS! THANK YOU THANK YOU THANK YOU! lol I really d
appreciate it, I've been banging my head into a wall for weeks trying t
figure it out myself

--
carg
-----------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...fo&userid=1527
View this thread: http://www.excelforum.com/showthread.php?threadid=39075

Msgbox Join(TNum,",")

Tim


"carg1" wrote in
message ...

Hi folks, I've searched up and down these forums, maybe I'm not
using
the right keywords, but I can't find an answer. I have a dynamic
array, TNum(), that gets filled with anywhere from 1 to 6 numbers
depending on the item name entered. The problem is, I want to make
a
message box that will output whatever numbers are in the array,
separated by commas. I tried a fixed array, but when the array had
less than 6 numbers, it'd return an out of bounds error, as I
expected.


I know my code is wrong (aside from the fact that it doesn't do what
I
want), but I feel I'm on the right track. I think all I need is a
way
to create the entire string first and then put it into the msgbox.
I
can't figure it out though. Pleeeaaase help meeee...:(


Code:
--------------------
Iput = InputBox("Please enter item:")
Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas,
lookat:=xlWhole, _
searchorder:=xlByRows)
FoundRow = FoundCell.Row

For i = 2 To 7

If Worksheets(1).Cells(FoundRow, i).Value < "" Then
j = j + 1

ReDim Preserve TNum(1 To j)
TNum(j) = Worksheets(1).Cells(FoundRow, i).Value

End If

Next i

For l = 1 To UBound(TNum())
Term2 = Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##") & ", " & _
Format(TNum(j), "####\.##\.##\.##")
--------------------


--
carg1
------------------------------------------------------------------------
carg1's Profile:
http://www.excelforum.com/member.php...o&userid=15271
View this thread:
http://www.excelforum.com/showthread...hreadid=390757