VBA Formula Array Problem

[nigelk]

Hi all,

I have the following array formula:

{=MIN(IF(AX11:BE11=0,"",AX11:BE11))}

which works fine if entered by hand.

But if I use the following:

Range(Cells(11, 58), Cells(CalcLR, 58)).FormulaArray = "=MIN(IF(AX11:BE11=0,"",AX11:BE11))"

I get the errror: " Unable to set the Formula array property of the Range class"

I know the range is set correctly. Can anyone help?

Thanks,Nigel

 

[AlphaFrog]

IF you want to express a literal quote character within a string, you need to use two quotes for each literal quote you want to express e.g.

"=MIN(IF(AX11:BE11=0,"""",AX11:BE11))"

That's how VBA interprets a Literal quote character from the quotes used to start\end the string

 

[Richard Schollar]

Hi Nigel

EDIT: Try using R1C1 style referencing within the formula which will probably be:

Range(Cells(11, 58), Cells(CalcLR, 58)).FormulaArray = "=MIN(IF(RC50:RC57=0,"""",RC50:RC57))"

 

[nigelk]

Thanks for the replies, but still having problems.

The first cells answer (3) is correct,

The formula in the cell is:

{=MIN(IF(RC50:RC57=0,"",RC50:RC57))}


but then the same answer and formula, is shown in all cells.


Strange thing is, if I check on another cell it shows that it's referencing the correct range.

 

[Richard Schollar]

I suspect you want this:

Cells(11, 58).FormulaArray = "=MIN(IF(RC50:RC57=0,"""",RC50:RC57))" 
 
Cells(11,58).Copy
 
Cells(11,58).Resize(CalcLR-11).offset(1).PasteSpecial xlPasteFormulas

 

[nigelk]

Hi Richard,

Now getting the message "You cannot change part of an array"

 

[nigelk]

Sorry Richard, my mistake.

Everything working ok now.

Many thanks for your help.

Nigel