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

Thread: .FormulaArray to insert array formula within a table

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default .FormulaArray to insert array formula within a table

    Hello all,

    I am in the process of writing a good-sized macro to automate the production of a particular report, so that all of the additional columns, pivot tables and other analysis drawing from data brought over from another report on a monthly basis can effortlessly be set up without the individual needing extensive knowledge of excel. I have only been using macros for a couple of days, but consider myself to otherwise be a strong excel user. I have also taken a formal class in Visual Basic some years ago, and although I am new to VBA in Excel I am quite comfortable working in it so far.

    The issue I have run into relates to using .FormulaArray to populate a column (actually, columns) inside a table. The goal is to get Excel to drop a huge array formula into each row within the last column of the table. I have of course already run into the error for trying to use more than 256 characters (can't remember the specific error code - might be the same one), but have found the .Replace work-around and hopefully should be good there. The main issue appears to be that apparently you can't use .FormulaArray if you want to insert the array formula into an already-named range, unless the range you are trying to insert the array formula into is the entire already-defined range ("You have to clear the array range first or change the entire array range at the same time" - http://www.xtremevbtalk.com/showpost.php?p=1285044&postcount=11").

    From what I understand, tables automatically define ranges based upon the appropriate column heading. In theory, I should be able to use .FormulaArray to drop the array function in, as long as I am sticking it into the entire column and not putting it into columns already containing data. Unfortunately, I am still running into the error even though I am putting it into an entire table column of empty cells. I have tried using the name of the table's defined range (which should be Range("ExpenseTable[I-ID Calc]" where "ExpenseTable" is the table name and "I-ID" is the column heading) as well as the cell reference (range("BK3:BK6354")) to no avail. I am getting the error even though the info I am trying to put into there is only a few characters long. When I use the same VBA code to a range outside of the table, it appears to work.

    Please see the below:
    Code:
        Dim TheFormula As String
        TheFormula = "=IFERROR(VALUE(1*MID([@Comments],MATCH(TRUE,ISNUMBER(1*MID([@Comments],ROW(R1:R100),1)),0),COUNT(1*MID([@Comments],ROW(R1:R100),1)))),"""")"
        
        'This is within the last column of my table, and doesn't work
        Worksheets("Expense").Range("BS3:BS50").FormulaArray = "=XXXX_XXXX"
        Worksheets("Expense").Range("BS3:BS50").Replace "=XXXX_XXXX", TheFormula
            
        'This is outside of the table, and does appear to work. However, I need to be able
        'to sort/filter the info with the other data in the table, so having this work
        'outside the table doesn't do much for me.
        Worksheets("Expense").Range("BK3:BK6354").FormulaArray = "=XXXX_XXXX"
        Worksheets("Expense").Range("BK3:BK6354").Replace "XXXX_XXXX", TheFormula
    On a side note, the .replace is not currently working for the range outside of the table. I am not too worried about this right now because I think I got it to work before, but please let me know if you happen to see an obvious error I made.

    This is my first post, so thanks for taking a look!

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: .FormulaArray to insert array formula within a table

    Hi and Welcome to MrExcel,

    Colin Legg provides an excellent article on using VBA to enter Array Formulas.

    Working With Range.FormulaArray In VBA | RAD Excel

    Here's an example using the Option 4 alternative that Colin suggests...

    Code:
    Sub Test()
     Dim TheFormula As String
    
     TheFormula = "=IFERROR(VALUE(1*MID([@Comments],MATCH(TRUE,ISNUMBER(1*MID([@Comments]," _
       & "ROW(R$1:R$100),1)),0),COUNT(1*MID([@Comments],ROW(R$1:R$100),1)))),"""")"
        
     With Sheets("Expense").Range("BS3:BS50")
       'step 1
       .Formula = "=XXXX_XXXX"
       .Replace "=XXXX_XXXX", TheFormula
       
       'step 2
       .FormulaArray = .FormulaR1C1
     End With
    End Sub
    Sounds like "TheFormula" you are using is just for testing, but just in case your actual array formula contains this expression, I believe you'll want to use absolute references for: ROW(R$1:R$100).
    Using Excel 2016

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: .FormulaArray to insert array formula within a table

    Jerry - thanks for the help, as the code pasted above was the result of a misinterpretation of some of the guidance provided by Colin. Your suggestion was very useful but I ended up going a different direction. The "TheFormula" w/replace was there because of the large array formula length - above 255/256 characters you have to use Replace in order to enter your array function using FormulaArray. Unfortunately, it looks like trying to use the FormulaArray with massive array functions (between 300-1000+ characters) caused more problems than it was worth to figure out FormulaArray & Replace. I ended up solving this using SendKeys to send Ctrl+Shift+Enter, which I normally would avoid but which ended up being a much simpler (although imperfect) solution in this situation.

    Thanks!

  4. #4
    New Member
    Join Date
    Jul 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: .FormulaArray to insert array formula within a table

    Quote Originally Posted by jemmell View Post
    Jerry - thanks for the help, as the code pasted above was the result of a misinterpretation of some of the guidance provided by Colin. Your suggestion was very useful but I ended up going a different direction. The "TheFormula" w/replace was there because of the large array formula length - above 255/256 characters you have to use Replace in order to enter your array function using FormulaArray. Unfortunately, it looks like trying to use the FormulaArray with massive array functions (between 300-1000+ characters) caused more problems than it was worth to figure out FormulaArray & Replace. I ended up solving this using SendKeys to send Ctrl+Shift+Enter, which I normally would avoid but which ended up being a much simpler (although imperfect) solution in this situation.

    Thanks!
    Hi Jemmel, can you please explain what do you mean by "using SendKeys" as I am also stuck with the issue of entering multi cell array formula in a Table column. Thanks.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,375
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: .FormulaArray to insert array formula within a table

    You can't enter a multi-cell array formula in a table column. If you want the same array formula in each row, just add the array formula to the first cell and the Table should autofill the rest.

  6. #6
    New Member
    Join Date
    Jul 2014
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: .FormulaArray to insert array formula within a table

    Quote Originally Posted by RoryA View Post
    You can't enter a multi-cell array formula in a table column. If you want the same array formula in each row, just add the array formula to the first cell and the Table should autofill the rest.
    Thank you Rory. Will do that.

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
  •