Use For each Statement to loop through multi-dimensional Array in VBA Excel - First dimension only

Question

I would like to know if it is possible to loop through a multi-dimensional array and limit the loop to the first dimension only.

I have worked out an example to ilustrate my point:

I use the data I have in excel to create a matrix and I want the loop to print only the values that correspond to the Index column. I have tried different things but nothing seems to work except when using if conditions to exit the for loop.

Data table

Here is the original code:

Public matrix1() As Variant

Sub elements_in_loop()

Range("A3").Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
matrix1 = Selection

For Each Record In matrix1

Debug.Print Record

Next

End Sub

here is the code with an if condition:

Public matrix1() As Variant

Sub elements_in_loop()

Range("A3").Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
matrix1 = Selection

For Each Record In matrix1

    If Record <= UBound(matrix1) Then

        Debug.Print Record
    
    Else:
    
        Exit For
    
    End If

Next

End Sub

Any advice on how to do it without an if condition is much appreciated.

Update:

What I need this information for is to look for similar records in two matrices without getting the error "Type mismatch".

Here's the data I'm using:

The record in yellow is the one that is in both matrices.

data updated

Here is the code I've worked out to get the records that match but is still using if conditionals to limit the search to the first dimension only.

Public matrix1() As Variant
Public matrix2() As Variant

Sub elements_in_loop()

Range("A3").Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
matrix1 = Selection

Range("G3").Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
matrix2 = Selection

For Each Record In matrix1

    If Record <= UBound(matrix1) Then
    
            For Each Record2 In matrix2
            
                If Record2 <= UBound(matrix2) Then
                        
                        If matrix1(Record, 4) = matrix2(Record2, 4) Then
                        
                                Debug.Print "There's match"
                                Debug.Print Record
    
                        Else: End If
                Else:
                
                        Exit For
                        
                End If
                
            Next
            
    Else:
    
        Exit For
    
    End If

Next


End Sub

use a normal For Loop For i = Lbound(Matrix,1) to Ubound(Matrix,1) then you would refer to the item Debug.Print Matrix(i,1) — Scott Craner
– Scott Craner, Commented Feb 4, 2021 at 21:47

Scott Craner · Accepted Answer · 2021-02-04 22:07:33Z

2

just use a regular for loop and loop the first dimension:

Sub elements_in_loop()
    With ActiveSheet
        matrix1 = .Range(.Range("A3").End(xlToRight), .Range("A3").End(xlDown))
        matrix2 = Range(Range("G3").End(xlToRight), Range("G3").End(xlDown))
        
        Dim i As Long
        For i = LBound(matrix1, 1) To UBound(matrix1, 1)
            Dim j As Long
            For j = LBound(matrix2, 1) To UBound(matrix2, 1)
                If matrix1(i, 4) = matrix2(j, 4) Then
                    Debug.Print "There's match"
                    Debug.Print matrix1(i, 4)
                End If
            Next j
        Next i
    End With
End Sub

answered Feb 4, 2021 at 22:07

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ricardo Diaz · Accepted Answer · 2021-02-04 21:57:09Z

0

An alternative is to work with the range directly

See how to set a reference and loop through the first column cells

Public Sub elements_in_loop()
    
    ' Define the sheet holding the range
    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet ' This could be a specific sheet like ThisWorkbook.Worksheets("Sheet'sName")
    
    ' Set an initial cell
    Dim initialCell As Range
    Set initialCell = sourceSheet.Range("A3")
    
    ' Set the range with Ctrl + Arrows (there are some alternatives like use the range CurrentRegion see below)
    Dim rangeToScan As Range
    Set rangeToScan = sourceSheet.Range(initialCell.End(xlToRight), initialCell.End(xlDown))
    
    ' This replaces the previous line
    'Set rangeToScan = initialCell.CurrentRegion
    
    ' Loop through cells in first column
    Dim sourceCell As Range
    For Each sourceCell In rangeToScan.Columns(1).Cells
            
        Debug.Print sourceCell.Value
    
    Next sourceCell

End Sub

Let me know if it works

answered Feb 4, 2021 at 21:57

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

5 Comments

Scott Craner Over a year ago

Why? This will be much much slower than iterating an array.

Ricardo Diaz Over a year ago

Performance wise you're right. I just gave an alternative

Dave S. Over a year ago

@ScottCraner I've worked out a solution with the advice you gave me. It works quite good, thank you.

Scott Craner Over a year ago

@DaveS. see my answer.

Dave S. Over a year ago

@RicardoDiaz thank you for your alternative as well but I definitely need to work with arrays.

Collectives™ on Stack Overflow

Use For each Statement to loop through multi-dimensional Array in VBA Excel - First dimension only

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related