CODEWITHSUNDEEP
vbaexcel
sjishan
sjishan

Reputation: 3672

VBA Excel iterate on multiple range

Currently I am writing the code in repittative fashion but I want to now how can I use loop on multiple range to achieve the same outcome.

I am getting error if I put two ranges in For Each.

Sub test_Click()

If (Range("C3").Value <> Range("C6").Value) Then
    Range("B3").Value = 5

End If

If (Range("D3").Value <> Range("D6").Value) Then
    Range("C3").Value = 5

End If


If (Range("E3").Value <> Range("E6").Value) Then
    Range("D3").Value = 5

End If

End Sub

Upvotes: 0

Views: 57

Answers (2)

DJK
DJK

Reputation: 9274

Could be done with the offset command in a for loop

For i = 0 To 3
    If (Range("C3").Offset(0, i).Value <> Range("C6").Offset(0, i).Value) Then
        Range("B3").Offset(0, i).Value = 5 
    End If
Next

Upvotes: 1

YowE3K
YowE3K

Reputation: 23994

There is no reason why you should be getting an error in your For Each statement. (But, then again, I don't understand what you mean by "if I put two ranges in For Each".)

Sub test_Click()
    Dim cel As Range
    For Each cel in Range("C3:E3").Cells
        If cel.Value <> cel.Offset(3, 0).Value Then
            cel.Offset(0, -1).Value = 5
        End If
    Next        
End Sub

Upvotes: 3

Related Questions