How to get index of Element in two dimensional array?

Question

I have an array with 2 dimensions. I also have a For Each loops which loops with elements of these arrays.

How can i get a Index of vElement/vElement2 in the moment of my comment here in code? I would be very, very thankful if You can help me.

For Each vElement In Table1

    For Each vElement2 In Table2
        If ws_1.Cells(1, c) = vElement Then
            For Row = 3 To lastRow
                    amountValue = amountValue + ws_1.Cells(Row, c).value
                    ws_2.Cells(row2, colIlosc) = amountValue
'Here i would love to have index of vElement for example. In my head it would be something like... Index(vElement) or Index(Table1(vElement))

                    ws_2.Cells(row2, columncodeprod) = vElement2
                    row2 = row2 + 1
                amountValue = 0
            Next Row
        End If
    Next vElement2
Next vElement

Answer 1

There is NO index in the case you put in discussion... vElement and vElement2 variables are of the Variant type. They are not objects, to have an Index property.

When you use a For Each vElement In Table1 loop, VBA starts from the array first element, goes down up to the last row and then do the same for the next column.

When you need to know what you name arrays 'indexes' you must use For i = 1 To Ubound(Table1, 1) followed by For j = 1 To Ubound(Table1, 2). In such a case you will know the matching array element row and columns. We can consider them your pseudo-indexes...

If you really want/insist to extract such indexes in an iteration of type For Each vElement In Table1, you must build them. I will try en elocvent code example:

Sub testElemIndex()
 Dim sh As Worksheet, Table1 As Variant, vElement As Variant
 Dim i As Long, indexRow As Long, indexCol

  Set sh = ActiveSheet

  sh.Range("C6").value = "TestIndex"
  Table1 = sh.Range("A1:E10").value
  For Each vElement In Table1
    i = i + 1:
    If vElement = "TestIndex" Then
        If i <= UBound(Table1, 1) Then
          indexRow = i: indexCol = 1
        Else
          indexCol = Int(i / UBound(Table1, 1)) + 1
          indexRow = i - Int(i / UBound(Table1, 1)) * UBound(Table1, 1)
        End If
        Debug.Print Table1(indexRow, indexCol), indexRow, indexCol: Stop
    End If
  Next
End Sub

You can calculate the rows and columns of the array element. And the code proves that using them, the returned array value is exactly the found one...

Is it a little more light on the array 'indexes'...?

Answer 2

Show Indices of an element in a 2-dim Array - the complicated way

If I understand correctly, you are looping through a datafield array via a ►For Each construction and want to get the current row/column index pair of that same array.

In order to answer your question

"How to get indices of an element in a two dimensional array",

I leave aside that you would get these automatically in a more evident and usual way if you changed the logic by looping through array rows first and inside this loop eventually through array columns - see Addendum *).

To allow a reconstruction of e.g. the 6th array element in the example call below as referring to the current index pair (element i=6 ~> table1(3,2) ~> row:=3/column:=2) it would be necessary

• to add an element counter i by incrementing its value by +1 each time you get the next element and
• to pass this counter as argument (additionally to a reference to the datafield) to a help function getIndex()

returning results as another array, i.e. an array consisting only of two values: (1) the current array row, (2) the current array column:

Example call

Note: For better readibility and in order to condense the answer to the mimimum needed (c.f. MCVE) the following example call executes only one For Each loop over the table1 datafield array; you will be in the position to change this to your needs or to ask another question.

Option Explicit                         ' declaration head of your code module                     

Sub ShowIndicesOf2DimArray()
    Dim table1                          ' declare variant 1-based 2-dim datafield
    table1 = Sheet1.Range("A2:B4")      ' << change to sheets Code(Name)

    Dim vElem, i As Long
    Dim curRow As Long, curCol As Long  ' current row/column number
    For Each vElem In table1

        i = i + 1                       ' increment element counter
        curRow = getIndex(table1, i)(1) ' <~ get row index via help function 
        curCol = getIndex(table1, i)(2) ' <~ get col index via help function 

        'optional debug info in VB Editors immediate window (here: Direktbereich)
        Debug.Print i & ". " & _
                " Table1(" & curRow & "," & curCol & ") = " & vElem & vbTab;
        Debug.Print ", where curRow|curCol are " & Join(getIndex(table1, i), "|")
    Next vElem
End Sub

Help function getIndex() called by above procedure

Function getIndex(table1, ByVal no As Long) As Variant
'Purpose: get 1-based 1-dim array with current row+column indices
    ReDim tmp(1 To 2)
    tmp(1) = (no - 1) Mod UBound(table1) + 1
    tmp(2) = Int((no - 1) / UBound(table1) + 1)
    getIndex = tmp
End Function

*) Addendum - "the simple way"

Just the other way round using row and column variables r and c as mentioned above; allows to refer to an item simply via table1(r,c) :

Sub TheSimpleWay()
    Dim table1                          ' declare variant 1-based 2-dim datafield
    table1 = Sheet1.Range("A2:B4")      ' << change to sheets Code(Name)
    Dim vElem, i As Long
    Dim r As Long, c As Long            ' row and column counter
    For r = 1 To UBound(table1)         ' start by row 1 (1-based!) up to upper boundary in 1st dimension
        For c = 1 To UBound(table1, 2)  ' start by col 1 (1-based!) up to upper boundary in 2nd dimension
            i = i + 1
            Debug.Print i & ". " & _
                " Table1(" & r & "," & c & ") = " & table1(r, c) & vbTab;
            Debug.Print ", where row|col are " & r & "|" & c

        Next c
    Next r
End Sub

Answer 3

Dim Table1() As Variant
Dim Table2() As Variant
Table1 = Range(Cells(2, 3), Cells(lastRow, vMaxCol))
Table2 = Range(Cells(2, 1), Cells(lastRow, 1))

Table1 is Variant(1 to 33, 1 to 9) Table2 is Variant(1 to 33, 1 to 1)

This 33 and 9 is dynamic.