Please help me figure out the problem in My Formula - Programmers Heaven

Howdy, Stranger!

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


Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Please help me figure out the problem in My Formula

florance001florance001 Posts: 1Member
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


  • DaiMitnickDaiMitnick Posts: 77Member
    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.