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

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.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ezil" wrote:

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

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)

"ezil" <(E-Mail Removed)> wrote in message
news:AD1275CF-EBF1-4AD1-9A05-(E-Mail Removed)...
> 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))"

or


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

Mike
"ezil" wrote:

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