Copy Formula Array to Range using VB.net
Posted on 2012-08-28
I have a program that creates a spreadsheet report in a very specific format (from column order to special color formatting, etc..) The data is stored in an SQL database over a few tables. So what I have is Sheet 1 of the spreadsheet contains the header records from table 1, and Sheet 2 contains the detail records, pertaining to the header records, from table 2. I specified that the header records be dumped, starting at a specific column in order, onto Sheet 1. I then dumped the detail records in Sheet 2, as is. What I was hoping to do was create an array formula for Sheet 1, based on an array from Sheet 2. I have successfully done this, and am able to add the formula to the first cell, in a range, where I need the detail totals to display. To simplify, Sheet 2 has Part#, Quantity, and Order#. Sheet 1 has the detail record for the customer. I wanted to display the quantity from Sheet 2, on Sheet 1, where the order # matches and in the column (with part# heading) where the part # matches. This gives them their colorful report, in the order they are so used to. Here is my code to create the formula, and insert it into the beginning cell of the range:
oSheet = oWB.Worksheets(1)
oSheet.Cells(5, 9).FormulaArray = _
My problem is I can't copy this formula to the rest of the range, and allow the dynamic variables to change. The closest I got was copying to the range, but it copied the absolute formula, and did not change values for the variables that should. I have been through Google, and other forums and tried several solutions (Autofill I thought would be the closest), but it fails.
The range I want to copy to, is from I5 (which contains my first formula) to FG(x) where x represents the last row where data exists (so as not to copy to blank rows where no data exists). I had a similar question about just a regular formula and I think the code for the range I want is:
oSheet.Range("I5", oSheet.Range("FG" & oSheet.Rows.Count).End(-4162))
I'm at a loss, since it's an array (unless it's just painfully obvious and I can't see it). I would really appreciate some help with this.