entering individual array formulas with vba

  • Thread starter Thread starter tom
  • Start date Start date
T

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..
 
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
 
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)
 
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.
 
Back
Top