VBA Array Formula

[Guest]

Hi,
How do you write Array Formulas using VBA? Range(cell).FormulaR1C1 = xxx doesn't seem to work

 

[Ivan F Moala]

On 2002-02-28 23:23, Anonymous wrote:
Hi,
How do you write Array Formulas using VBA? Range(cell).FormulaR1C1 = xxx doesn't seem to work

Range(cell).FromulaArray = xxx


Ivan

 

[tamirkorem]

Try this

Range("A1").Select
ActiveCell.Formula = "=SUM(B1:B3)"
As a result
A1 will have the sum of B1,B2 and B3

 

[•Daniel•]

sht.Range(Cells(4, 19), Cells(x, 19)).FormulaArray = "=INDEX(Sábana!R2C2:R400C2,MATCH(1,(RC12=Sábana!R2C3:R400C3)+(RC12=Sábana!R2C4:R400C4)+(RC12=Sábana!R2C5:R400C5)+(RC12=Sábana!R2C6:R400C6)+(RC12=Sábana!R2C7:R400C7)+(RC12=Sábana!R2C8:R400C8)+(RC12=Sábana!R2C9:R400C9)+(RC12=Sábana!R2C10:R400C10)+(RC12=Sábana!R2C11:R400C11)+(RC12=Sábana!R2C12:R400C12)+(RC12=Sábana!R2C13:R400C13)+(RC12=Sábana!R2C14:R400C14)+(RC12=Sábana!R2C15:R400C15),0))"

This formula works with ".formular1c1" but no with formularray, runtime error 1004, any ideas why? thx