Results 1 to 10 of 10
Like Tree1Likes
  • 1 Post By Eric W

VBA FormulaArray

This is a discussion on VBA FormulaArray within the Excel Questions forums, part of the Question Forums category; I am having some trouble with inserting an Array formula LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1) "",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1) "",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")" With Worksheets("Sheet1").Range("A5") .FormulaArray = LR_Formula ...

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    21

    Default VBA FormulaArray

    I am having some trouble with inserting an Array formula

    LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")"

    With Worksheets("Sheet1").Range("A5")
    .FormulaArray = LR_Formula
    .Value = .Value
    End With


    this is just one of a few scenarios I have tried and am open to others

    the Error is invalid property range. I am aware it is > 255 characters, however I have tried other array formulas under 255 and still not working

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    3,693

    Default Re: VBA FormulaArray

    Without parsing out that entire formula, and without worrying about the 255 length yet, I see at least one problem:

    LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")"

    To define a string, you put it between quote marks (") which I've marked in red. If you want to include a quote mark within your string, you need to double it ("") so that Excel recognizes that it's part of the string, and not a delimiter. If you have 2 quote marks together, which I've marked in blue, you need to double each one, meaning you need to use """" instead for all the marked sections.

    Check Rick's comments in posts 3 and 4 of this thread for a more detailed explanation:
    http://www.mrexcel.com/forum/excel-q...iven-cell.html

    As far as the length, that's a thornier question.
    Last edited by Eric W; Jan 6th, 2017 at 05:09 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    3,693

    Default Re: VBA FormulaArray

    Here's one possible solution to the length problem:

    Entering Long Array Formulas in VBA – Daily Dose of Excel
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    New Member
    Join Date
    Mar 2016
    Posts
    21

    Default Re: VBA FormulaArray

    Thank you for the responses, I am using the common work around for arrays > 255 characters, but I am still running into an issue with the property class. I am hoping its just a syntax error


    Range("A10").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X""),"""")"
    Range("A10").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    21

    Default Re: VBA FormulaArray

    I just noticed I had one extra parenthesis at the end of the replacement, but it still didn't fix the issue

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    3,693

    Default Re: VBA FormulaArray

    I still see 1 instance of the "" issue:

    Range("A10").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Mar 2016
    Posts
    21

    Default Re: VBA FormulaArray

    still no dice after fix; same error

    Range("A5").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X""),"""")"
    Range("A5").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    3,693

    Default Re: VBA FormulaArray

    Try:

    Range("A5").FormulaArray = "=IFERROR(IF(ROWS($A$10:A10)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),""X_X_X"")),"""")"

    Range("A5").Replace """X_X_X""", "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$10:A10))))"

    Even that may not work, since I haven't parsed your entire formula to see what it does. If you still have problems, select the entire formula from the first line, including the surrounding quote marks, copy it, then go to the immediate window and type PRINT and paste the formula. It should print out the formula with all the quotes resolved. Then copy that version and paste it into a cell in Excel, and make sure that you don't get any errors there. If so, fix them, then reverse the process with the "" marks.
    bsquad likes this.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    21

    Default Re: VBA FormulaArray

    Eric W,

    thank you for the assist, I was able to figure out an solution/alternative

    Area_1 = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),1)),"""",INDEX(INDIRECT($B$1),X_X_X)),"""")"
    Area_2 = "SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"""",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROW(INDIRECT($B$1))-ROW('Indv Source (Exp Dis)'!$M$17)+1),ROWS($A$5:A5))"
    With Sheet17.Range("A5")
    .FormulaArray = Area_1
    .Replace "X_X_X", Area_2
    End With

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    3,693

    Default Re: VBA FormulaArray

    Glad you got it working!

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
  •  


DMCA.com