IF(cell=1,Countif(Range(R2:R11),"=1"),"0") in VBA

[UseLessFuel]

Hello,
I'm struggling with applying a formula down a column, with VBA. The formula in cell AT13 is:

=IF(AL12=1,COUNTIF(R2:R11,"=1"),"0")

which I can extend down to the last row of data, but I would like to use VBA, to automate it.

The code below does not work, and it looks like I may need to use Application.WorksheetFunction.CountIf("R3:R12", "=1"), but I am unsure how to go about it. Perhaps I need to use R1C1 method for the Range, or is there another way?

Sub Z1FlagsD30_2()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'Helper column in AT -checks status of the TEN previous D30 if Z1Last10=1


Dim lr As Long


lr = Cells(Rows.Count, "A").End(xlUp).Row


     With Range("AT13:AT" & lr)
            .Formula = "=IF(AL13=1,COUNTIF(R3:R12,"=2"),0)"
            .Value = .Value
        End With
      
        
 
Application.ScreenUpdating = True 'turn it back on
Application.Calculation = xlCalculationAutomatic


End Sub

 

[Fluff]

Try

            .Formula = "=IF(AL13=1,COUNTIF(R3:R12,""=2""),0)"

You need to double-up on the quotes inside a formula in VBA

 

[shg]

Or ...

.Formula = "=IF(AL13 = 1, COUNTIF(R3:R12, 2), 0)"

 

[UseLessFuel]

Thank you both @Fluff and [Mention]shg[/Mention]. I had forgotten about the double quotes and didn't know about having no quotes inside! Both worked.

 

[Fluff]

Glad we could help & thanks for the feedback