PDA

View Full Version : Solved: Looping through a two dimensional array, displaying element values, having problems



swahrenburg
01-19-2013, 11:36 AM
What i am trying to do is loop through a two dimensional array that takes values from their respective cell locations and then display them one at a time in a message box. This simple code works fine until the last iteration is executed. After the last iteration it says that i have "a type mismatch error". any help would be greatly appreciated.

-----here is the code-------

Sub arrayfun()
Dim arraytest(1 To 5, 1 To 2) As Double
For j = LBound(arraytest) To UBound(arraytest)
For i = LBound(arraytest) To UBound(arraytest)
arraytest(i, j) = ThisWorkbook.Sheets("sheet1").Cells(i, j).Value
MsgBox (arraytest(i, j))
Next
Next
End Sub

p45cal
01-19-2013, 12:13 PM
try:Sub arrayfun()
Dim arraytest(1 To 5, 1 To 2) ' As Double
For j = LBound(arraytest, 2) To UBound(arraytest, 2)
For i = LBound(arraytest) To UBound(arraytest)
arraytest(i, j) = ThisWorkbook.Sheets("sheet11").Cells(i, j).Value
MsgBox arraytest(i, j) & " i=" & i & ", j=" & j
Next
Next
End Sub
I commented out the As Double because if there's text in the cell you'll get your type mismatch. Now it'll accept anything.
You would later get a subscript out of range error because both loops would be iterating from 1 to 5. Correction for that takes the form of the red 2s

swahrenburg
01-19-2013, 01:17 PM
Thanks for the informative and quick reply. i think i get it now.

very well done.

Teeroy
01-19-2013, 03:37 PM
In this case, just to set the array, it would be simpler to use a variant and assign the range values directly to it.

Sub QuickArray()
Dim arraytest 'becomes variant type
arraytest = ThisWorkbook.Sheets("sheet11").Range("A1:B5")
MsgBox arraytest(1, 1) ' Array is r5 x c2 per your example
End Sub

snb
01-20-2013, 05:28 AM
sub M_snb()
sn=ThisWorkbook.Sheets("sheet1").Range("A1:B5")

for each it in sn
msgbox it
next
end Sub

swahrenburg
01-21-2013, 10:09 AM
thank you for all your help everyone. i am relatively new to programing and love how everyone in the community is so helpful, supportive and progressively trying to move the whole community forward.