Looping through two cell ranges of same length in VBA given Range object

user_10528459 0
Viewed 3292

Let's say I have code like this:

For Each cell1 In range_vals
    For Each cell2 In range_pred

      If (cell1 = cell2) Then
            //Do something
      End If

    Next cell1
Next cell2

But what I want to do is iterate through both cell ranges at the same time and this ranges are of the length, like this:

For Each cell1, cell2 In range_vals, range_pred   

      If (cell1 = cell2) Then
            //Do something
      End If

Next cell1, cell2

I know this can be done in python, however I'm struggling with doing it in VBA.

vba excel loops
3 Answers

Instead of using For Each, try using For like this:

Dim range_vals As Range
Dim range_pred As Range
Dim i As Integer

With Worksheets("Sheet1")
   Set range_vals = .Range("A1:A10")
   Set range_pred = .Range("B1:B10")

   For i = 1 To range_vals.Cells.Count
      If range_vals.Cells(i) = range_pred.Cells(i) Then
         MsgBox "do something"
      End If
   Next
End With

This will loop in 'parallel', comparing A1 to B1, A2 to B2 and so on.

user_51620730

The first code is used when your range is predetermined and the second one is used when you have a dynamic range.

Option Explicit

Sub test()

    Dim rng1 As Range, rng2 As Range, cell1 As Range, cell2 As Range

    With Worksheets("Sheet1") '<=Change if needed

        Set rng1 = .Range("A1:A10") '<=Change if needed
        Set rng2 = .Range("B1:B10") '<=Change if needed

        For Each cell1 In rng1
            For Each cell2 In rng2
                If cell1 = cell2 Then
                    MsgBox "Bingo"
                End If
            Next cell2
        Next cell1

    End With

End Sub

Sub test1()

    Dim LR1 As Long, LR2 As Long, i As Long, j As Long
    Dim Value1 As String, Value2 As String

    With Worksheets("Sheet1") '<=Change if needed

        LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row '<=Change if needed
        LR2 = .Cells(.Rows.Count, "B").End(xlUp).Row '<=Change if needed

        For i = 1 To LR1
            Value1 = .Range("A" & i).Value
                For j = 1 To LR2
                    Value2 = .Range("B" & j).Value
                    If Value1 = Value2 Then
                        MsgBox "Bingo"
                    End If
                Next j
        Next i

    End With

End Sub
user_67744720

Looping through 2 ranges in the same time cannot be done.

However, accessing the Row and the Column within the Range could be quite useful, as they are based on the range, and not on the worksheet. Thus, the first column of of the first cell of Range("C5:C10") is always column 1. The first row is row 1. And the first cell of that range could be accessed like this:

Debug.Orint Range("C5:C10").Cells(1,1).Address

with printing $C$5. With this knowledge one can loop through one range and access Cells of another one:

Public Sub TestMe()

    Dim myRangeA As Range
    Dim myRangeB As Range

    With Worksheets(1)
        Set myRangeA = .Range("A1:A5")
        Set myRangeB = .Range("B21:B25")
    End With

    Dim myCellA As Range
    Dim myCellB As Range

    For Each myCellA In myRangeA
        Set myCellB = myRangeB.Cells(myCellA.Row, myCellA.Column)
        Debug.Print myCellA.Address
        Debug.Print myCellB.Address
    Next myCellA

End Sub

Using Offset() when possible is a better solution - If myCell = myCell.Offset(0,1) Then.

user_54486260
Related