.FormulaArray to insert array formula within a table

[jemmell]

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:

    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!

 

[Jerry Sullivan]

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...

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).

 

[jemmell]

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!

 

[Malick]

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.

 

[RoryA]

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.

 

[Malick]

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.