SumIF applied to a dynamic range

**timtim89** · 02-23-2013, 10:17 PM

Hey all,

I hope to apply a SumIF function to a range defined by a few variables (lastRow, lastCol ect.). I prefer the result left in the cells as just the number, but getting the result by applying a formula in the cell will work as well. I have tried a few things, but couldn't make it work, so I really hope someone can help me out. Please see attached file, as it shows better what I need done. Thanks a lot in advance for you effort!
To make it easier I took most of the code out, leaving this:

Sub makro1()

Dim i As Variant
Dim lastRow As Integer
Dim actCol As Integer
Dim actRow As Integer
Dim lastCol As Integer

'the variables are determined in the code I took out, but will vary from time to time changing according to the data.
lastCol = 11
lastRow = 10
actCol = 5
actRow = 6
i = 3

With Cells(lastRow + 3, actCol).Resize(lastRow - actRow, i)

.Formula = Application.WorksheetFunction.SumIf(Range("$E$6:$K$6"), Range("E$12") & "*", Range("$E7:$K7"))

End With

End Sub

**timtim89** · 02-24-2013, 09:30 AM

I considered the .FormulaR1C1 method as well, but it didn't seem that I could determine the span of the formula with ranges. Any suggestions, please?

**Andrew-R** · 02-24-2013, 11:23 AM

I think what you're trying to do is this:

Dim actCol As Integer
Dim actRow As Integer
Dim lastCol As Integer

lastCol = 11
lastRow = 10
actCol = 5
actRow = 6
i = 3

With Cells(lastRow + 3, actCol).Resize(lastRow - actRow, i)
  .Formula = "=SumIf($E$6:$K$6,E$12& ""*"", $E7:$K7)"
End With

End Sub

If you're using WorksheetFunction.SumIF that's allowing you to run a SUMIF from within a macro, if you want to insert a formula you just write it into the text of the formula.

**timtim89** · 02-24-2013, 11:56 AM

Thanks for the answer Andrew. The thing is that the formula depends on the parameters, which will change from time to time. Therefore, I need a formula where the ranges are given by the parameters. I tried something like the following, but had to give up cause I simply couldn't figure out how to do it.

.FormulaR1C1 = "=SUMIF(R[" & -(lastRow - actRow + 3) & "]C[" & actCol & "]:R[" & actRow & "]C[" & lastCol & "],R12C&""*"",R[-6]C5:R[-6]C11)".......

or

.Formula = "=sumif(" & Cells(actRow, actCol).Address & ":" & Cells(actRow, lastCol).Address & ";" & Cells(lastRow + 3, _
Application.Cells().Column).Value & "" * "" & ";" & Cells(actRow + 1, actCol).Address & ":" & Cells(actRow + 1, lastCol).Address & ")"