For Loop with If-Or Statement VBA

Question

I have hard-coded for ever two cells in an excel column to count if either the first or second have a value of 1. It worked this way (but also written out for every cell applicable so is inefficent):

If Range("D10").Value = 1 Or Range("D11").Value = 1 Then
    x = x + 1
Else
    x = x + 0
End If

I am trying to refactor it. My thought was to do a for loop, with an if and an or statement (similar to in my current code), but receive a syntax error. My attempt:

Sub refactor()
Dim x As Integer
x = 0
For Each c In Range("D2-D13").Cells
    If c.Value =1 OR (c+1).Value = 1 Then
    x = x + 1
    Else
    x = x + 0

    End If
Range("A19") = x
End Sub

All I can find is for loops with if statements in them, but never the added "or" condition considered. VBA is new to me. Is there a way I can code this?

What is the point of a `For` loop (which considers one cell at a time) if you insist on an `If` condition that considers all the cells at the same time? — GSerg, Jan 08 '22 at 07:25
Aside, avoid any unqualified `Range` references. See canonical VBA post: [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/1422451) — Parfait, Jan 08 '22 at 17:36

score 0 · Answer 1 · answered Jan 08 '22 at 17:16

for loop in enough to run entire range itn't required "or c.offset(1,0).value = 1" because "or" operator used to any one condition is true it will allow to run the code.

"if c.Value = 1 and c.Offset(1, 0).Value = 1 Then" = this case is used for if both the statement is true then only if condition allow to run the code. are u looking for this?

Sub refactor()
Dim x As Integer
Dim c As Range

x = 0

For Each c In Range("D2:D13")
   'if c.Value = 1 Or c.Offset(1, 0).Value = 1 Then'this line is same as below line
    if c.value = 1 then
        x = x + 1
    End If
Next c

Range("A19") = x
End Sub

For Loop with If-Or Statement VBA

1 Answers1