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
Comments
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. |
------------------------------------------