vba Cells(x, 2).Formula = "= IF(" & Cells(y, 2) & ">=" & Cells(z, 2) & ",true, false)"

[montecarlo2012]

Hello.
I am working out this idea

sub test0()
Cells(15, 2).Formula = "=IF(" & Cells(2, 2) & ">=" & Cells(7, 2) & ",TRUE, FALSE)"
Cells(31, 2).Formula = "=IF(" & Cells(18, 2) & ">=" & Cells(23, 2) & ",TRUE, FALSE)"
Cells(47, 2).Formula = "=IF(" & Cells(34, 2) & ">" & Cells(39, 2) & ",TRUE, FALSE)"
Cells(63, 2).Formula = "=IF(" & Cells(50, 2) & ">=" & Cells(55, 2) & ",TRUE, FALSE)"
Cells(79, 2).Formula = "=IF(" & Cells(66, 2) & ">=" & Cells(71, 2) & ",TRUE, FALSE)"
Cells(95, 2).Formula = "=IF(" & Cells(82, 2) & ">=" & Cells(87, 2) & ",TRUE, FALSE)"
end sub

From here I tried to loop, in order to make it shorter. like this:

Sub test1()
Dim y%
Dim z%
Dim x%

y = 2
z = 7

      For x = 15 To 95 Step 16      
                        Cells(x, 2).Formula = "= IF(" & Cells(y, 2) & ">=" & Cells(z, 2) & ",true, false)"            
            y = y + 16
            z = z + 16            
      Next x

End Sub

but give me an error

debug show me this line

Cells(x, 2).Formula = "= IF(" & Cells(y, 2) & ">=" & Cells(z, 2) & ",true, false)"

What I did wrong.
Please let me know.
Thanks for reading.

 

[Marc L]

Hi, try this :​

Sub Demo1()
    For R& = 0 To 80 Step 16
        Cells(15 + R, 2).Formula = "=" & Cells(2 + R, 2) & ">=" & Cells(7 + R, 2)
    Next
End Sub

 

[Marc L]

But the obvious Excel way :​

Sub Demo2()
    For R& = 15 To 95 Step 16:  Cells(R, 2).FormulaR1C1 = "=R[-13]C>=R[-8]C":  Next
End Sub

 

[montecarlo2012]

Thank you Mr. Marc.
This is what I get

 

[Marc L]

​

Just declare the variable with Dim statement like in your Test1 VBA procedure or remove Option Explicit …​

 

[montecarlo2012]

well now I am here

I delete option explicit also.
and still.
Thanks for your input

 

[Michael M]

you need double quotations in the formula...see in red
Cells(x, 2).Formula = "=IF(" & Cells(y, 2) & "" >= "" & Cells(Z, 2) & ",true, false)"

 

[DanteAmor]

Try this:

Sub test1()
  Dim x As Long
  For x = 15 To 95 Step 16
    Cells(x, 2).Formula = "=IF(" & Cells(x - 13, 2).address & ">=" & Cells(x - 8, 2).address & ",TRUE, FALSE)"
  Next
End Sub

 

[montecarlo2012]

Thank you Michael.

 

[Michael M]