-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to create an array formula using VB
- Thread starter SCFM
- Start date
DonkeyOte
MrExcel MVP
- Joined
- Sep 6, 2002
- Messages
- 9,124
It's not clear what you mean... you can calculate in memory using Evaluate, eg:
or if you wanted to write back to Excel you could use the .FormulaArray property
However the same formula could be achieved using SUMPRODUCT which is not an Array.
Code:
MsgBox Evaluate("SUM(B2:C2*B3:C3)")or if you wanted to write back to Excel you could use the .FormulaArray property
Code:
Cells(1,"A").FormulaArray = "=SUM(B2:C2*B3:C3)"However the same formula could be achieved using SUMPRODUCT which is not an Array.
Code:
=SUMPRODUCT(B2:C2,B3:C3)
Upvote
0
DonkeyOte
MrExcel MVP
- Joined
- Sep 6, 2002
- Messages
- 9,124
If you opt to x-post in other forums please have the courtesy to provide links accordingly... this holds true for all forums.
http://www.excelforum.com/excel-programming/720677-how-to-create-an-array-formula-using-vb.html
http://www.excelforum.com/excel-programming/720677-how-to-create-an-array-formula-using-vb.html
Upvote
0
Here is the one that solved my issue:
Cells(1,"A").FormulaArray = "=SUM(B2:C2*B3:C3)"
Thank you Mr. DonkeyOte and really sorry for the x-post.
Upvote
0
Why an array formula?
Are you sure whatever formula you want/need couldn't be done with a 'normal' formula?
My interest was about how to use/best way to use an array formula in VB. I utilized a test example. My real array formula is:
Cells(iRow, "H").FormulaArray = "=MAX(IF(ISNUMBER(0+MID(F" & iRow & ", 1, ROW($1:$4))),0+MID(F" & iRow & ",1,ROW($1:$4))))+IF(ISNUMBER(MATCH(RIGHT(F" & iRow & ",1),$I$2:$I$27,0)),VLOOKUP(RIGHT(F" & iRow & ",1),$I$2:$J$27,2,0)/10000,0)".
Regards
Upvote
0
DonkeyOte
MrExcel MVP
- Joined
- Sep 6, 2002
- Messages
- 9,124
FWIW the same formula can be achieved using non-array.
Using F2 as an example
=LOOKUP(9.99E+307,--MID(0&F2,1,ROW(1:5)))+SUMIF($I$1:$I$27,RIGHT(F2),$J$1:$J$27)/10000
I would also make the point that rather than applying a formula via iteration (as implied by use of iRow) you should apply the formula to a range en masse if viable, eg:
Using F2 as an example
=LOOKUP(9.99E+307,--MID(0&F2,1,ROW(1:5)))+SUMIF($I$1:$I$27,RIGHT(F2),$J$1:$J$27)/10000
I would also make the point that rather than applying a formula via iteration (as implied by use of iRow) you should apply the formula to a range en masse if viable, eg:
Code:
Range("H2:H100").Formula = "=LOOKUP(9.99E+307,--MID(0&F2,1,ROW($1:$5)))+SUMIF($I$1:$I$27,RIGHT(F2),$J$1:$J$27)/10000"
Upvote
0
Similar threads
- Question
