Using VBA to put a COUNTA formula in a cell

[zookeepertx]

Hello again!
I'm trying to put a formula into a cell that reads like the following example: =COUNTA(C10"C12) but it simply refuses to do so.
The range of cells to be referenced varies, so in my code I've tried to address that but keep getting a "Compile error: Expected: end of statement". Can someone tell me what's wrong? It seems like it should be pretty easy since I don't want to use the COUNTA as part of the actual code, I just want to enter the formula into a cell!
I get the error after I type the code, not when I try to run it. I can't even get to the running it part!
The error shows up at the "B" in this line: Range("J" & i).Formula = "=COUNTA("B" & r & ":" & "B" & i - 1)" in the below example of code

r = 6
For i = 6 To lr Step 1
    If Range("B" & i).Value = "Total" Then
        Range("B" & i).Formula = "=COUNTA("B" & r & ":" & "B" & i - 1)"
        Rows(i + 1).Insert Shift:=xlDown
        Range("C" & i & ":" & "I" & i).ClearContents
    End If
r = i + 1
Next i

I thought maybe it wanted me to make it a separate For/Next, so then I tried this, but no luck.

r = 6
For i = 6 To lr Step 1
    If Range("B" & i).Value = "Total" Then
        Range("B" & i).Select
        Selection.Formula = "=COUNTA("B" & r & ":" & "B" & i - 1)"
    End If
r = i + 1
Next i

r = 6
For i = 6 To lr Step 1
    If Range("C" & i).Value = "Total" Then
        Rows(i + 1).Insert Shift:=xlDown
        Range("C" & i & ":" & "I" & i).ClearContents
    End If
r = i + 1
Next i

I'm feeling like an idiot because I should be able to figure this out but frankly, I'm just sick of looking at it. I've spent way too much time on it already today and I just need some help, PLEASE!

Thanks!

Jenny

 

[zookeepertx]

You're welcome. Glad it helped.

BTW, I think that you need to revisit your signature (click your user name at the top right of the forum then Signature) as it does not agree with your profile information at the left of your post.

View attachment 84195

You can probably just remove the version information from your signature since it is shown all the time at the left anyway.

Son of a gun, you're right; I'm so silly! I probably would never have caught that! Thanks for the heads-up.