.FormulaArray to insert array formula within a table

jemmell

New Member
Joined
Apr 7, 2014
Messages
2
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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).
 

jemmell

New Member
Joined
Apr 7, 2014
Messages
2
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

New Member
Joined
Jul 10, 2014
Messages
18
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 10, 2014
Messages
18
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,408
Messages
5,528,603
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top