Outputting the contents of an array
 

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

Outputting the contents of an array
 

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

Outputting the contents of an array
 

:eek: 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

Outputting the contents of an array
 

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