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

 

[Micron]

Range("B" & i).Select
Selection.Formula = "=COUNTA(B" & r & ":B" & i - 1 & ")"

Don't know what to say now, except that apparently you have a code solution coming as per post 2.

 

[zookeepertx]

Range("B" & i).Select
Selection.Formula = "=COUNTA(B" & r & ":B" & i - 1 & ")"

View attachment 83846
Don't know what to say now, except that apparently you have a code solution coming as per post 2.

Well, I sure appreciate you trying to help. Have a great weekend!

 

[Peter_SSs]

Do you really need a formula or would just the answer be okay?
If formula not required then you could try this. If it does what you want, then you wouldn't need to separately delete the word "Total" each time as this code will do that as it goes.
Test with a copy of your data.

Sub Enter_Count()
    Dim rA As Range
   
    For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count + 1).Value = rA.Count
    Next rA
End Sub

 

[zookeepertx]

Do you really need a formula or would just the answer be okay?
If formula not required then you could try this. If it does what you want, then you wouldn't need to separately delete the word "Total" each time as this code will do that as it goes.
Test with a copy of your data.

Sub Enter_Count()
    Dim rA As Range
  
    For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count + 1).Value = rA.Count
    Next rA
End Sub

It does need to be a formula because I'll need to be deleting some rows and adding some rows within each country's group of files and the count needs to keep up with the correct number.
Another part of my macro puts a formula into some other cells - that one's just a formula to divide N by M (if I remember the columns correctly, although I'm not looking at it right now) - and it does exactly that. I don't understand how it can just refuse to even let the line be placed in the code? I'm not asking it to actually calculate anything; if I just want it to put what's inside the quotes into a cell, why does it care what it is that I want to put there?
(I'm blithering now; it's almost 3:00 in the morning and I really should go to sleep, LOL!)
Thanks for the reply. I still have hopes for a solution at some point. This board has never failed me!

 

[Peter_SSs]

It does need to be a formula

Then according to your second image in post #4, try this

Sub Enter_Count_v2()
    Dim rA As Range
   
    For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count + 1).Formula = "=COUNTA(" & rA.Offset(-1).Resize(rA.Count + 1).Address(0, 0) & ")"
    Next rA
End Sub

 

[zookeepertx]

Then according to your second image in post #4, try this

Sub Enter_Count_v2()
    Dim rA As Range
  
    For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count + 1).Formula = "=COUNTA(" & rA.Offset(-1).Resize(rA.Count + 1).Address(0, 0) & ")"
    Next rA
End Sub

AHA! That works perfectly! I don't understand HOW it works, but it does! Thank you!

Now that the COUNTA problem is solved, can I also place a formula in the same row in J-M to sum the same rows that the COUNTA counts? I thought I might be able to adapt the formula you made to add a SUM to those columns but I failed miserably. (I just hoped to be capable of doing SOMETHING on my own. )

I tried just redoing your For loop, changing it to the appropriate column, but it adds another row and sums all the rows that are now above it. (As with the COUNTA cell, there's already a number in each of those columns in the row at the bottom of each country's section, but it's just a static number. It needs to be a sum for the same reason as the COUNTA - because I'll be deleting and adding rows within each country and need the sum to keep up with the new totals on those columns.)

Jenny

 

[zookeepertx]

Well, I had an epiphany and figured it out, but I think it's the long way around.
That piece of code is now the following:

Dim rA As Range
   
    For Each rA In Range("B5", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count + 1).Formula = "=COUNTA(" & rA.Offset(-1).Resize(rA.Count + 1).Address(0, 0) & ")"
    Next rA

    For Each rA In Range("J5", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count).Formula = "=SUM(" & rA.Offset(-1).Resize(rA.Count).Address(0, 0) & ")"
    Next rA

    For Each rA In Range("K5", Range("K" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count).Formula = "=SUM(" & rA.Offset(-1).Resize(rA.Count).Address(0, 0) & ")"
    Next rA

    For Each rA In Range("L5", Range("L" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count).Formula = "=SUM(" & rA.Offset(-1).Resize(rA.Count).Address(0, 0) & ")"
    Next rA

    For Each rA In Range("M5", Range("M" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlNumbers).Areas
      rA.Cells(rA.Count).Formula = "=SUM(" & rA.Offset(-1).Resize(rA.Count).Address(0, 0) & ")"
    Next rA

That seems to be kind of an unwieldy way to do it, but it that's how it has to be, then so be it. But is there a cleaner way to accomplish this?

 

[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

 

[zookeepertx]

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

PERFECT! That's amazing! Thank you so much. This will save me lots of time; I never know for each month end reporting how many different countries are going to appear on this report and it gets so tedious trying to make sure every one of them gets the formulas in there correctly. I've been doing it manually forever, so this will be awesome.
Have a great day!

(Now I just have a conditional formatting issue, but I'll start another topic for that. Tomorrow. Probably...)

Jenny

 

[Peter_SSs]

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.

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