Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
entering individual array formulas with vba
Hi, I have an array I a range of cells I neeed to fill individually
with array formulas, BUT, I this is a seperate array formula for each cell, (=SUMPRODUCT(Normalised!$A1:$W1,TRANSPOSE(Pcomps!A $2:A$24)) if you must know) using the range.formulaarray property in VBA, just assigns the same formula to the entire range, which is fine for most array formulas but for mine I want the relative references to be usesd. I can achieve the desired result using a for next loop, but its very slow (this is a big range 3,000*30 ish) are there anyways to get the relative references to be recognised as they would be if using the range.formula property??? thanks for any ideas.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
entering individual array formulas with vba
Maybe you could use a different formula:
Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1:f20") myRng.Formula = "=mmult(Normalised!$A1:$W1,Pcomps!A$2:A$24)" End Sub tom wrote: Hi, I have an array I a range of cells I neeed to fill individually with array formulas, BUT, I this is a seperate array formula for each cell, (=SUMPRODUCT(Normalised!$A1:$W1,TRANSPOSE(Pcomps!A $2:A$24)) if you must know) using the range.formulaarray property in VBA, just assigns the same formula to the entire range, which is fine for most array formulas but for mine I want the relative references to be usesd. I can achieve the desired result using a for next loop, but its very slow (this is a big range 3,000*30 ish) are there anyways to get the relative references to be recognised as they would be if using the range.formula property??? thanks for any ideas.. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
entering individual array formulas with vba
That actually returns an array: ={999} (if you hit F2|F9 to see the result)
This will return a simple value: =INDEX(MMULT(Normalised!$A1:$W1,pcomps!A$2:A$24),1 ,1) Dave Peterson wrote: Maybe you could use a different formula: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1:f20") myRng.Formula = "=mmult(Normalised!$A1:$W1,Pcomps!A$2:A$24)" End Sub tom wrote: Hi, I have an array I a range of cells I neeed to fill individually with array formulas, BUT, I this is a seperate array formula for each cell, (=SUMPRODUCT(Normalised!$A1:$W1,TRANSPOSE(Pcomps!A $2:A$24)) if you must know) using the range.formulaarray property in VBA, just assigns the same formula to the entire range, which is fine for most array formulas but for mine I want the relative references to be usesd. I can achieve the desired result using a for next loop, but its very slow (this is a big range 3,000*30 ish) are there anyways to get the relative references to be recognised as they would be if using the range.formula property??? thanks for any ideas.. -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
entering individual array formulas with vba
Excellent! Thanks,
I'd dismissed MMULT as useless earlier because of its limits on multiplying matrices bigger than 51 or whatever it is. I didn't think to use it as an alternative way to calculate the dotproduct. Thanks again. Dave Peterson wrote in message ... That actually returns an array: ={999} (if you hit F2|F9 to see the result) This will return a simple value: =INDEX(MMULT(Normalised!$A1:$W1,pcomps!A$2:A$24),1 ,1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Array Range & Individual Cells | Excel Discussion (Misc queries) | |||
Entering an array formula | Excel Worksheet Functions | |||
entering formula in a numeric array | Excel Discussion (Misc queries) | |||
entering as an array | Excel Discussion (Misc queries) | |||
Entering Array Functions | Excel Discussion (Misc queries) |