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

 

[Peter_SSs]

Just put all the formulas in as the loop goes through (once only). See if this is sufficient.

Sub Enter_Formulas()
  Dim rA As Range
 
  For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
    With rA.Cells(rA.Count + 1)
      .Formula = "=COUNTA(" & rA.Offset(-1).Resize(rA.Count + 1).Address(0, 0) & ")"
      .Offset(, 8).Resize(, 5).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
    End With
  Next rA
End Sub

 

[Joe4]

Can you show us what your expected result is?
Give us the first 3 cases, i.e.
- The address of the first cell you want this formula in, and the exact formula in that cell
- The address of the second cell you want this formula in, and the exact formula in that cell
- The address of the third cell you want this formula in, and the exact formula in that cell

Once I see this pattern, I think I should be able to come up with the code you want.

 

[Micron]

The message means that something is not correctly delimited. Perhaps you want
Range("B" & i).Formula = "=COUNTA(""B"" & r & "":B"" & i -1)"""

or using single quotes instead of delimited double quotes
Range("B" & i).Formula = "=COUNTA('B' & r & ':B' & i -1)"""

Note that it is not necessary to separate your ":"

 

[zookeepertx]

Hi Joe,

I'll have to post screenshots since this is my work computer and I can't use the hosting websites suggested in the rules.
This is what the sheet looks like after I run the part of my macro that works so far.

Then I've got code that clears all instances of the word "Total". (which does work) Then, under "Shipment Count" I need to put a formula to count the cells in B that have data for that Country. I also need to put a formula J-M of the same row to sum the data for that country. (I tried to do that the same way as the COUNTA, thinking SUM might be easier, but had the same problem.)

Thanks for the help!

 

[zookeepertx]

The message means that something is not correctly delimited. Perhaps you want
Range("B" & i).Formula = "=COUNTA(""B"" & r & "":B"" & i -1)"""

or using single quotes instead of delimited double quotes
Range("B" & i).Formula = "=COUNTA('B' & r & ':B' & i -1)"""

Note that it is not necessary to separate your ":"

Thanks for the answer, but it doesn't like either one of those.

 

[Micron]

OK, sorry it didn't work. Your posted version did cause a compile error (syntax) for me but neither of the ones I posted did.

 

[Micron]

Hmm, maybe
"=COUNTA(B" & r & ":B" & i - 1 & ")"

May not be the exact result you want in your sheet but syntax seems to work for me.

 

[zookeepertx]

Hmm, maybe
"=COUNTA(B" & r & ":B" & i - 1 & ")"

May not be the exact result you want in your sheet but syntax seems to work for me.
View attachment 83839

Hmmm, still doesn't work over here, but you did remind me that I need to combine the colon with the second B without the extra quotation marks and the "&", so that makes it at least LOOK better. But I still get the error. (Maybe I'm just not holding my mouth right, LOL!)

 

[Micron]

Did I just discover that the line you say raises an error is not in any of the code you posted? Where is

Range("J" & i)