Dynamic arrays and type mismatches
 

Okay, you guys helped me immensely, your suggestions work great, but I'm
having one more problem (that I sort of created for myself :rolleyes: ).
The problem is that I need to be able to output each of the values for
items that have less than 6 values. I'm only 50% successful. I
figured I needed a loop, so I declared an integer "l" and made this
loop:


Code:
--------------------
For l = 0 To j
Term2 = MsgBox("The values for this item a" & vbCr & vbCr & _
TNum(j) & ",", vbYesNoCancel + vbDefaultButton2, "Values")
Next l
--------------------


What it does, however, is display the values one at a time in the
message box. Then, for lack of better words, doubles the amount of
values shown. As in, if the values in the cells it looks at are 150
and 1783, I'll get 4 message boxes, the first saying "150,", I'll hit a
button, the next says "1783,", hit the button, and two more will pop up,
both containing "1783,".

I'm just trying to make the loop display each of the values with commas
in between each. I'll have to figure a way to make it omit the comma
on the last value.


Formula:
--------------------
Sub Search()
'

Dim TNum() As String, Term As String, Term2 As String, Iput As String, Iput2 As String
Dim j As Integer, k As Integer, l As Integer
Dim FoundCell As Range, FoundRow As Integer
j = 0

Iput = InputBox("Please enter item:") 'Input
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 = 0 To j
Term2 = MsgBox("The values for this item a" & vbCr & vbCr & _
TNum(j) & ",", vbYesNoCancel + vbDefaultButton2, "Values")
Next l
--------------------


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