entering individual array formulas with vba

[tom]

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]

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

 

[Dave Peterson]

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)

 

[tom]

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.