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:a1000,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:a1000,a2:a100))"

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

--
HTH

Bob

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

"ezil" wrote in message
...
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:a1000,a2:a100))"

or


Range("b3").Select
Selection.FormulaArray = "=MIN(if(a2:a1000,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:a1000,a2:a100))"