Nesting a formula in a macro

  • Thread starter Thread starter Richard
  • Start date Start date
R

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.
 
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?
 
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
 
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
 
Back
Top