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..

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

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

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)