Solved

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 = _

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

oSheet = oWB.Worksheets(1)

oSheet.Cells(5, 9).FormulaArray = _

"=SUM(IF(Sheet2!$A$1:$A$25

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(-41

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.

10 Comments

expected-result.xls

=SUMIFS(Sheet2!$B:$B,Sheet

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!

You should be able to assign that formula to any cells in columns I through AG of Sheet1.

```
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)"
```

although I am curious that autofill did not work for you. How did you implement it?

oSheet.Range("I5", oSheet.Range("FG" & oSheet.Rows.Count).End(-41

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?

```
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)"
```

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!

oSheet.Range("I5", "FG" & oSheet.Range("E" & oSheet.Rows.Count).End(-41

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

From Excel how can I import data from SSRS Reporting Services ? | 6 | 27 | |

Vba and formula to change date | 4 | 17 | |

get the maximum value on a condition | 3 | 24 | |

extract numbers / text from a column | 2 | 23 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!