VBA Array formula

[ziad alsayed]

dear all

this is an array formula,i press ( CTRL+SHIFT+ENTER) for it to work in excel

how can i write it in VBA?

=SUM(VLOOKUP(RC1,'PHC 2011'!C1:C86,MATCH(CONCATENATE(R1C1,R4C),CONCATENATE('PHC 2011'!R4,'PHC 2011'!R5),0),0),VLOOKUP(RC1,'Apapa 2011'!C1:C86,MATCH(CONCATENATE(R1C1,R4C),CONCATENATE('Apapa 2011'!R4,'Apapa 2011'!R5),0),0),VLOOKUP(RC1,'VI 2011'!C1:C86,MATCH(CONCATENATE(R1C1,R4C),CONCATENATE('VI 2011'!R4,'VI 2011'!R5),0),0),VLOOKUP(RC1,'Kano 2011'!C1:C86,MATCH(CONCATENATE(R1C1,R4C),CONCATENATE('Kano 2011'!R4,'Kano 2011'!R5),0),0),VLOOKUP(RC1,'Ikeja 2011'!C1:C86,MATCH(CONCATENATE(R1C1,R4C),CONCATENATE('Ikeja 2011'!R4,'Ikeja 2011'!R5),0),0))

let say i want to put in cells(5,3).

appreciate any help

 

[Luiz]

Just use FormulaArray. A sinple example to invert the matrix in D4:F6 puting result at A1:C3

Range("A1:C3").FormulaArray = "=MINVERSE("D4:F6")"

 

[ziad alsayed]

Sorry, i didn't get it, please can you write how my formula Should be?

thanks in advance

 

[Luiz]

Sorry, I made a mistake. This is the correct formula>

Range("A1:C3").FormulaArray = "=MINVERSE(D4:F6)"

In Sheet1 type (for instance):

1 2 3
4 5 5
7 7 7

in range D4:F6. One number per cell.

In VBA create a Module and type

Public Sub Inversion()
Range("A1:C3").FormulaArray = "=MINVERSE(D4:F6)"
End Sub

Running the macro you'll get your CTRL+SHIFT+ENTER
You can try TRANSPOSE, MMULT, etc.

 

[ziad alsayed]

i tried the example, an it works, but this is not what i need

the result of my formula is only in one cell.
simple what it does , os that it sum cells from many sheets.

any other idea??

thanks for your help.

 

[ziad alsayed]

dear all

any other idea??

thanks in advance