array formula in macro

[Guest]

The following array formula is working when directly written in cell. But I
have to do
this through a macro. How to write the following command line correctly in a
macro.

Range("b3") = "=MIN(if(a2:a100>0,a2:a100))"

 

[Guest]

The best starting point is to put the Macro recorder on (Tools > Macro...)
then enter the formula, then turn off the Macro recorder. Have a look at the
code it generated in a new module.

 

[Bob Phillips]

Range("b3").Value = activesheet.Evaluate("MIN(IF(A2:A100>0,A2:A100))")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

or


Range("b3").Select
Selection.FormulaArray = "=MIN(if(a2:a100>0,a2:a100))"

Mike