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.

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?

In article ,
"Richard" wrote:

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.

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

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

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