Questions

VBA in excel help. looping through a two dimensional array

+
0 Votes
Locked

VBA in excel help. looping through a two dimensional array

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
  • +
    0 Votes
    mendskyz

    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

    +
    0 Votes
    Rojavida

    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

    +
    0 Votes
    Rojavida

    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)

    +
    1 Votes
    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

  • +
    1 Votes
    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

    +
    0 Votes
    Rojavida

    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)

    +
    0 Votes
    Rojavida

    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

    +
    0 Votes
    mendskyz

    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