Dynamic Sumif in VBA

[jeffreybrown]

Hi All,

Not sure if you'll need to see more of the code to help answer this question. Basically I have a set of data which through a macro expands based on how many different values in another column. As the data expands subtotals are added and at the end I have a Sumif to get the overall totals. I'm having trouble understanding how to get this Sumif to be dynamic so it accomdates however big the range of data is.

Cells(Rows.Count, "AS").End(xlUp).Offset(2).Resize(5, 5).FormulaR1C1 = "=SUMIF(R1C44:R65C44,RC44,R1C:R65C)"

 

[DonkeyOte]

Jeff, I'm assuming the first formula (in the 5 x 5 matrix of results) is going into column AS in the last row offset by 2 rows, correct ?

Given the 5 x 5 matrix the traditional approach of R1C44:R[-2]C44 etc MAY not work given that the [-2] adjustment would obviously include some of the prior summation results as you progress down the Matrix... as I say this MIGHT not be an issue IF Column AR entries in the summation range are unique, given the formula is a SUMIF I suspect this is the case in which case the below may well work for you:

Cells(Rows.Count, "AS").End(xlUp).Offset(2).Resize(5, 5).FormulaR1C1 = "=SUMIF(R1C44:R[-2]C44,RC44,R1C:R[-2]C)"

You could store the last row of data (pre results matrix) in a variable and use that:

Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count,"AS").End(xlUp).Row
Cells(lngLastRow+2,"AS").Resize(5,5).FormulaR1C1 = "=SUMIF(R1C44:R" & lngLastRow & "C44,RC44,R1C:R" & lngLastRow & "C)"

The latter is safer but the former quicker to implement (and likely to be ok in this context)

 

[jeffreybrown]

Hi Luke,

Yes you are right it is column AS and offset by 2. I just tested it (the first formula) and it worked fine. Can't say I still understand the R1C1 style, but I keep trying.

I'm still a little perplexed at how the 2nd argument in the sumif knows how to reference the correct value in column AR. If the data keeps growing or shiriking I don't see how it knows the placement of the value i.e., 0004?

 

[DonkeyOte]

Re: R1C1

Anything appearing within [ ] is a relative adjustment

Consider: Active Cell is B10

R[-5]C[1]

equates to C5 as we subtract five rows from current row (10) and add 1 column to current column (2)

R[5]C[-1]

equates to A15 as we add five rows to current row and subtract one column from current column (ie move left)

Any numbers that follow R/C that are not encased within [ ] are essentially "absolutes"

R4C[1]

equates to C4 .. ROW is fixed to row 4 but Column is a relative adjustment (+1)
If you applied the same formula to B11 you would still get C4 (row does not adjust)... if you applied the same formula to say A11 you would get B4 - the row remains fixed but the column adjustment is relative so becomes B (A + 1 column)

If no values follow the R/C then the current position is used...

RC

is equivalent to saying B10

R:R

is equivalent to saying 10:10

C:C

is equivalent to saying B:B

So going to your example:

=SUMIF(R1C44:R[-2]C44,RC44,R1C:R[-2]C)

You're using Ranges that always start in Row 1 (absolute) but where end row is relative to current row less 2 rows... the Columns are fixed for the initial Criteria range in the SUMIF but the Sum column is relative to the column in which the formula appears... if we assume for sake of demo that the first formula in the 5 x 5 matrix is going in AS100 then the above translates as:

=SUMIF($AR$1:$AR98,$AR100,AS$1:AS98)

The formula when applied say to the 2nd row and 2nd column of the 5 x 5, ie AT101 would be translated as follows:

=SUMIF($AR$1:$AR99,$AR101,AT$1:AT99)

So you see as the formula is pasted across the matrix of results the formulae will be begin to reference prior results (ie rows 100+) but as long as AR100:AR104 are unique it's not an issue as the values won't meet the SUMIF criteria and thus won't be included in the results.

I hope that helps.

 

[jeffreybrown]

Thanks again for the explanation...this is one I need to read a few more times just to grasp the concept in my head.