• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1685
  • Last Modified:

Copy Formula Array to Range using VB.net

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 = _
                        "=SUM(IF(Sheet2!$A$1:$A$25000=Sheet1!I$3,IF(Sheet2!$C$1:$C$25000=Sheet1!$E5,Sheet2!$B$1:$B$25000)))"

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.
0
fizzlefry
Asked:
fizzlefry
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
andrewssd3Commented:
I'm having trouble envisaging exactly what you are trying to do - are you able to post the spreadsheet - either the version you have created from VB or the desired result?
0
 
fizzlefryAuthor Commented:
andrewssd3, thank you so much for the reply.  I'm sorry to make it difficult.  Attached is a scaled down copy of my sheet.  Bear in mind, this only represents a small portion.  Sheet 1 and Sheet 2 could contain any number of rows, based on the number of orders.
expected-result.xls
0
 
tdlewisCommented:
Let's start with getting your formula right. Here's what I think you want:
=SUMIFS(Sheet2!$B:$B,Sheet2!$C:$C,"="&Sheet1!$E5,Sheet2!$A:$A,"="&Sheet1!I$3)

That says to add up all the cells in column B where column C matches the pick number in cell E5 and column A matches the product number in cell I3.

To get this to work anywhere in the spreadsheet, switch to FormulaR1C1 as follows:

oSheet.Cells(5, 9).FormulaR1C1 = "=SUMIFS(Sheet2!C2,Sheet2!C3,""=""&Sheet1!RC5,Sheet2!C1,""=""&Sheet1!R3C)"

You should be able to assign that formula to any cells in columns I through AG of Sheet1.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Rory ArchibaldCommented:
Since you are using an xls I guess you are using a version earlier than 2007? If so, you can use SUMPRODUCT rather than SUMIFS:

oSheet.Cells(5, 9).Formula = _
                        "=SUMPRODUCT((Sheet2!$A$1:$A$25000=Sheet1!I$3)*(Sheet2!$C$1:$C$25000=Sheet1!$E5),Sheet2!$B$1:$B$25000)"

Open in new window


although I am curious that autofill did not work for you. How did you implement it?
0
 
fizzlefryAuthor Commented:
tdlewis, thanks for the reply.  I tried your formula and it's working like a champ.  I appreciate it.  However, I'm still having trouble, and I'm sure this is a no-brainer.  Sadly, I apparently have no brain.  So this is the code I'm using to select the range to add the formula to:

oSheet.Range("I5", oSheet.Range("FG" & oSheet.Rows.Count).End(-4162)).FormulaR1C1 = "=SUMIFS(Sheet2!C2,Sheet2!C3,""=""&Sheet1!RC5,Sheet2!C1,""=""&Sheet1!R3C)"

What I thought this would do was go from I5 (upper left corner of the range) to FG(x) (lower right hand corner of the range).  However, all it's doing is starting at I5 and going straight down the row to FG and inputting the formula.  Then it jumps up 1 row and overwrites my case descriptors.  Now I know (-4162) is the constant for xlUp, which makes sens why it's moving up 1 row, but I'm confused why it's not going to the end of the range, and only the end of the row.  Any additional advice you could offer?
0
 
tdlewisCommented:
It's not a good idea to rely on oSheet.Rows.Count. When I did that on the spreadsheet you posted earlier, it filled down through row 65536. Here's some code that I think will do what you want:
    n = 5
    Do While Not IsEmpty(oSheet.Cells(n, 1).Value)
        n = n + 1
    Loop
    oSheet.Range("I5", "FG" & n).Cells.FormulaR1C1 = "=SUMIFS(Sheet2!C2,Sheet2!C3,""=""&Sheet1!RC5,Sheet2!C1,""=""&Sheet1!R3C)"

Open in new window

0
 
fizzlefryAuthor Commented:
tdlewis, I added your suggested code.  IsEmpty was not available for use (vb.net Visual Studio 2010).  I tried IsDBNull thinking that might accomplish the same thing.  It locked my system temporarily, then returned an HRESULT error.  Here is what I used:

Dim n As Integer
        n = 5
        Do While Not IsDBNull(oSheet.Cells(n, 1).Value)
            n = n + 1
        Loop
        oSheet.Range("I5", "FG" & n).Cells.FormulaR1C1 = "=SUMIFS(Sheet2!C2,Sheet2!C3,""=""&Sheet1!RC5,Sheet2!C1,""=""&Sheet1!R3C)"
0
 
Rory ArchibaldCommented:
Assuming column E has data for every row

oSheet.Range("I5", "FG" & oSheet.Range("E" & oSheet.Rows.Count).End(-4162).Row).FormulaR1C1 = "=SUMIFS(Sheet2!C2,Sheet2!C3,""=""&Sheet1!RC5,Sheet2!C1,""=""&Sheet1!R3C)"
0
 
tdlewisCommented:
rorya's suggestion accomplishes the same thing as that loop with
oSheet.Range("E" & oSheet.Rows.Count).End(-4162).Row

Open in new window

0
 
fizzlefryAuthor Commented:
Points split.  tdlewis' revised formula, and rorya's implementation throughout the sheet.  Thanks again, guys!  Another reminder why I come to the best collective of techies on the net.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now