Please help me figure out the problem in My Formula

florance001

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

Range("A11034").Select

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

Loop


End Sub

DaiMitnick

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:

[code]
ActiveCell.Offset(1).Select
[/code]


HTH, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------