CODEWITHSUNDEEP
vbaexcel
dan6657
dan6657

Reputation: 117

For Each Loop Ranges VBA Excel Macro

If Range("D32").Value = 2 Then
   If Range("D15").Value = 0 Then
      Range("D15").Value = 1
      Range("D32").Value = 1
   End If
End If

I've been trying to figure out how to put these values within a for each loop.

Basically if D15->AE15 = 2 and D32->AE32 = 0 change both of the values to 1.

I thought about repeating the above nested IF statement for every cell.. but that would take a bit of time.

For Each c In Worksheets("sheet1").Range("D32:AE32").Value

If Range("D32") = 2 Then


Range("D15").Value = 1
Range("D32").Value = 1

End If
Next c

That works for one cell. However i'm stumped on how to get it to check the whole row and then change the corresponding column values for 15 and 32 when one is 2.

Upvotes: 0

Views: 4771

Answers (2)

user1016274
user1016274

Reputation: 4209

Try this, you're on the right track: 

Sub ChgValues()
    Dim c As Range, d As Range

    For Each c In Range("D15:AE15")
        Set d = c.Offset(17, 0)
        If c.Value = 0 And d.Value = 2 Then
            c.Value = 1
            d.Value = 1
        End If
    Next c
End Sub

edit:
The above code will check for a 0 in row 15 and a 2 in row 32, for all columns from D to AE. If the values could also be the other way around (2 in row 15, 0 in row 32) then an additional comparison is needed:

change

If c.Value = 0 And d.Value = 2 Then

to

If (c.Value = 0 And d.Value = 2) or (c.Value = 2 And d.Value = 0) Then

Upvotes: 2

Taelsin
Taelsin

Reputation: 1090

You need to give the for each loop a collection and then check values.

Dim cell As Range
For Each cell In Range("D15:AE15").Cells
    If cell.Value = 0 And cell.Offset(17).Value = 2 Then
        cell.Value = 1
        cell.Offset(17).Value = 1
    End If
Next cell

Upvotes: 1

Related Questions