Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Array Formula

  1. #1
    Guest

    Default

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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2003
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Try this

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

  4. #4
    New Member
    Join Date
    Jun 2009
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Formula

    Code:
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •