Array Formulas

[irisqueiroz]

Please,


I'm tryng to write a dynamic code.


I just know how to write it in R1C1 mode, but I'd like to write it as A1 mode.


Range("B10").Select

Selection.FormulaArray = "=MMULT(MMULT(R7C2:R7C" & nAtivos + 1 & ",Matriz!R2C2:R" & nAtivos + 1 & "C" & nAtivos + 1 & "),TRANSPOSE(R7C2:R7C" & nAtivos + 1 & ")"


but its not working I dont know why...


The code is suposed to be:


"=MMULT(MMULT(Range(cells(2,2),cells(2,nAtivos+1)),Matriz!Range(cells(2,2),cells(nAtivos+1,nAtivos+1))),TRANSPOSE(Range(cells(2,2),cells(2,nAtivos+1)))


Thnaks!

 

[Luke M]

I think this is what you are looking for:

[pre]

Dim nAtivos As Integer
Dim fString As String
nAtivos = 5
fString = "=MMULT(MMULT(B2:" & Cells(2, nAtivos + 1).Address(False, False) & ",Matriz!B2:" & _
Cells(nAtivos + 1, nAtivos + 1).Address(False, False) & "),TRANSPOSE(B2:" & _
Cells(2, nAtivos + 1).Address(False, False) & "))"
MsgBox "Formula will be:" & vbNewLine & fString, vbOKOnly

Range("B10").FormulaArray = fString

[/pre]

 

[Colin Legg]

Luke's given you a great tip there: use a

String

variable to build up the formula string. This makes your code much easier to debug because you can use the VBA IDE's tools to interrogate the string variable (to see the string itself, how long it is etc) to determine what the problem is with it.

 

[jeffreyweir]

Yes, good tip. I'd add that if NOT using R1C1 notation, then it's often worth referring to named ranges in your code rather than something like Matriz!B2, so that your code is robust in the case that someone adds or deletes a row or column above or to the left of the cell your VBA references.