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
Variable With An Array Formula
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Variable With An Array Formula
Regards,
John
John
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Variable With An Array Formula
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?
Or did you mean something else?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Variable With An Array Formula
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
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
Regards,
John
John
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Variable With An Array Formula
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.
ActiveCell.FormulaArray = "..."
ActiveCell.Value = ActiveCell.Value
This will first assign the array formula to the cell, then replace the formula with its result.
Best wishes,
Hans
Hans