moving a "count" range

[sramsey]

I'm creating a macro where my count() range changes by 1 everyday. For
example, on day 1, my count range would be =count(a1:a30). On day 2 my range
would be =count(a2:a31), day 3 would be (a3:a32) and so on. So far I think
it may look something like:

Range("J20").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[9]C[2]:R[13]C[2])"
Range("J21").Select

only much better, with a +1 in there somewhere...

Any ideas?

 

[Don Guillett]

One possible Formula if a simple count(K:K)won't do
=COUNT(INDIRECT("K1:K"&MATCH(9999,K:K)))

 

[Don Guillett]

Use THIS. change K:K to a:a and 5 to 30 and 4 to 29
=SUM(K:K)-IF(COUNT(K:K)>5,SUM(K1:INDEX(K:K,COUNT(K:K)-4)),0)

 

[J_Knowles]

Try this out:

Sub ColJFormula()
Range("J30").FormulaR1C1 = "=COUNT(R[-29]C[-9]:RC[-9])"
Range("J30").AutoFill Destination:=Range("J30:J50"), Type:=xlFillDefault
Range("J30").Select ' to see where formula starts
End Sub


HTH,