Question

Locked

VBA in excel help. looping through a two dimensional array

By swahrenburg ·
I need help with this vba hand written macro. i am trying to loop through a two dimensional array with elements that takes values from their respected cell locations. I want to iterate through all the array elements and assign each element a value that is a number within the excel cell location and message the wale. it works fine until the last iteration is completed. once the last iteration is completed it says, "type 13 error type mismatch" any help would be greatly appreciated.

thanks in advance!
---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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

VBA in excel help. looping through a two dimensional array

by swahrenburg In reply to VBA in excel help. loopin ...

I need help with this vba hand written macro. i am trying to loop through a two dimensional array with elements that takes values from their respected cell locations. I want to iterate through all the array elements and assign each element a value that is a number within the excel cell location and message the wale. it works fine until the last iteration is completed. once the last iteration is completed it says, "type 13 error type mismatch" any help would be greatly appreciated.

thanks in advance!
---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

Collapse -

2D array looping

by Rojavida In reply to VBA in excel help. loopin ...

I think your problem is with UBound and Lbound not being specifically tied to the correct dimension. Ubound is set to 5 and Lbound is set to 1 at the beginning, so each of your loops tries to go round 5 times (5 columns of 5 rows). When it gets to the 3rd column, you get a type mismatch because (presumably) there is not a numerical value in cell C1.
Try using:
Ubound(arraytest,1) and Ubound(arraytest,2)

Collapse -

Try this

by Rojavida In reply to VBA in excel help. loopin ...

Sub arrayfun()

Dim arraytest(1 To 5, 1 To 2) As Variant


For i = LBound(arraytest, 1) To UBound(arraytest, 1)


For j = LBound(arraytest, 2) To UBound(arraytest, 2)

arraytest(i, j) = ThisWorkbook.Sheets("sheet1").Cells(i, j).Value

MsgBox (arraytest(i, j))

Next
Next

End Sub

Collapse -

VBA in excel help. looping through a two dimensional array

by mendskyz In reply to VBA in excel help. loopin ...

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, 1) To UBound(arraytest, 1)
arraytest(i, j) = ThisWorkbook.Sheets("sheet1").Cells(i, j).Value
Debug.Print (arraytest(i, j)), i, j
Next
Next

End Sub

Back to Software Forum
5 total posts (Page 1 of 1)  

Software Forums