Results 1 to 4 of 4

Thread: HELP VBA Array Formula CSE (Ctrl+Shift+Enter)

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default HELP VBA Array Formula CSE (Ctrl+Shift+Enter)

    Can someone tell me why this line isnt working?

    ActiveCell.FormulaArray = "=IFERROR(INDEX($C$2:$C$2000,SMALL(IF($F$372=$A$2:$A$2000,ROW($A$2:$A$2000)-ROW($B$2)+1),1)),"")"

    i get "unable to set the formula array"

    i know the formula works so... what gives???

    im using this one on Excel 2010.

    Thanx in advance.
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,262
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP VBA Array Formula CSE (Ctrl+Shift+Enter)

    Try this,

    Code:
    ActiveCell.FormulaArray = "=IFERROR(INDEX($C$2:$C$2000,SMALL(IF($F$372=$A$2:$A$2000,ROW($A$2:$A$2000)-ROW($B$2)+1),1)),"""")"
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HELP VBA Array Formula CSE (Ctrl+Shift+Enter)

    Quote Originally Posted by lrobbo314 View Post
    Try this,

    Code:
    ActiveCell.FormulaArray = "=IFERROR(INDEX($C$2:$C$2000,SMALL(IF($F$372=$A$2:$A$2000,ROW($A$2:$A$2000)-ROW($B$2)+1),1)),"""")"

    That tottaly worked!!!!

    thanx!
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,262
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP VBA Array Formula CSE (Ctrl+Shift+Enter)

    No problemo. Quotes in VBA can be a pain.

    ───────────────────░█▓▓▓█░▇▆▅▄▃▂
    ──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
    ─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
    ───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
    ────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
    ───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
    ─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
    ──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
    ───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
    ────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

Some videos you may like

User Tag List

Tags for this Thread

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
  •