Variable With An Array Formula

[jstevens]

Is it possibe to assign a variable an array formula within VBA?

I can assign the ActiveCell an array formula such as:
ActiveCell.FormulaArray = "=SUM((Output!I$3:I$50000=Send!A45)*(Output!F$3:F$50000<=Send!$B$2)*Output!K$3:K$50000)"


Thanks for taking a look,
John

[HansV]

Do you want to assign the result (outcome) of an array formula to a variable? If so, you'll have to assign the array formula to a cell, as in your example, and assign the value of that cell to the variable. You can't directly assign the result of an array formula to a variable, as far as I know.

Or did you mean something else?

[jstevens]

Hans,

You read into it correctly. I thought it may be possible to assign the value of the array formula to the variable first. Then I could take the variable's value and enter it directly to the cell.

Oh well, you learn something new everyday.

Regards,
John

[HansV]

You could do the following:

ActiveCell.FormulaArray = "..."
ActiveCell.Value = ActiveCell.Value

This will first assign the array formula to the cell, then replace the formula with its result.