Please help me figure out the problem in My Formula

I have these data with 24, 000 rows of data, I have no problem about the loop and the range, I think the problem is in my .FormulaR1C1 as I am not sure how to use the double qoutes within the variable "Myrow". I am new to VBA macros. Your help is much appreciated!

Here is my code:

Sub datacellCount()

Dim Myrow As Long


Do Until IsEmpty(ActiveCell)

Myrow = Range(ActiveCell, ActiveCell.End(xlDown)).Count - 2
ActiveCell.Offset(0, 4).FormulaR1C1 = "=COUNTA(RC[-3]:R[Myrow]C[-3])"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=COUNTIF(RC[-3]:R[Myrow]C[-3],""1"")"
ActiveCell.Offset(Myrow + 1).Select


End Sub


  • Could you be more specific about what doesn't work, does it stop on a line asking you to debug, or does it run and you find the outcome isn't as expected?

    The quotes you have are fine, the only things I can see that would cause a problem here, is either you are trying to insert an invalid formula (i.e. if say you put a formula into B7 that does a countif on previous 10 rows, it will fail), or perhaps the last line, if you are looking to just go to the next line, you need to remove the Myrow bit, i.e. should look like:


    HTH, Dai

    Do or do not, there is no try. |
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion