Richard,

I'm not sure exactly what you're after, however here are several examples of
what can be done.
Try them out, and see which one works best for you.

Range("L1").Formula = "=COUNT(I2:I100)"
Range("L1").Formula = "=COUNT($I$2:$I$100)"

Range("L1").FormulaR1C1 = "=COUNT(R[1]C[-3]:R[99]C[-3])"
Range("L1").FormulaR1C1 = "=COUNT(R2C9:R100C9)"

Range("L1").Value = Application.Count(Range("I2:I100")) ' Places the
value in L1


regards,

anon-e-mouse

I have the following at the beginning of a macro:

R = Range("L1").Value

Is there a way to replace "L1" by the formula used in L1
itself.

Before excecuting the macro I put the following formula in
L1: =COUNT(I2:I100).