Nesting a formula in a macro

[Richard]

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

Basically, is it safe to directly replace ("L1") by its
formula?

Thanks for any help.

 

[J.E. McGimpsey]

I'm confused: if COUNT(I2:I100) = 10, say, what would

R = Range("=COUNT(I2:I100)").Value

or

R = Range(10).Value

or

R = Range(Range("L1").Formula).Value

represent?

 

[JohnI in Brisbane]

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

 

[JohnI in Brisbane]

Richard,

Oh, one more thing:-

You can also do the do the following where "R" is a variable:-

R = Application.Count(Range("I2:I100"))

regards,

JohnI

 

[Richard]

That was what I was after.
You covered everything and it worked!!

Thanks.