Offset() Reduce.

[=?gb2312?B?Q2FjdHVzIFvPycjLx/Jd?=]

I build a variational length record sheet.
that record length is
Sheet1!B1="=Countif(A:A,">0")"

that record Range is ("A3:A" & B1),
How to convert the String into Range?

this is fail case.
Sheet1!B3="=AVERAGE("A3:A" & B1)"
this case is pass.
Sheet1!B3="=AVERAGE(OFFSET(A3,0,0,B1,1))"

I want to know more method.

thanks.

 

[Bob Phillips]

Is this what you want

"=AVERAGE(""A3:A" & Range("B1") & """)"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[=?gb2312?B?Q2FjdHVzIFvPycjLx/Jd?=]

Thank Bob


Public Function Eval(expr As String) As Variant
Eval = Evaluate(expr)
End Function


=AVERAGE(Eval("A3:A"&B1))
or
=Eval("AVERAGE(A3:A" & B1 &")")





"Bob Phillips"