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:
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!
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!