SUMIFS with a dynamic range and criteria fields in VBA

[EggcellExperiments]

Hi everyone!

Trying to do a SUMIFS in VBA with the following setup, but lacking the knowledge to execute it correctly:

=SUMIFS(sum_range, criteria_range1, criteria1)
1. sum_range = dynamic range six columns to the left of the active cell's position
2. criteria_range1 = dynamic range nine columns to the left of the active cell's position
3. criteria1 = stored two cells to the left of the active cell's position in a table

I figured out how to select the column six columns to the left of my activated cell's position:
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

I tried plugging it into a basic SUM formula but am getting a syntax error.

ActiveCell.FormulaR1C1 = "=SUM(ActiveCell.Offset(0, -6).Range("A1:A200"))"

Trying to figure out how to store each part of the formula for use. I recorded a macro in R1C1 notation but it only works once since each month, a new data set is appended to the worksheet in the same format to create a time series of data.

Any insights or examples would be appreciated.

Thanks in advance,

EE

 

[aaewalsh]

try this:

Sub SumIfsVBA()

Dim sumRange As Range
Set sumRange = ActiveCell.Offset(0, -6).Resize(ActiveCell.CurrentRegion.Rows.Count, 1)

Dim criteriaRange As Range
Set criteriaRange = ActiveCell.Offset(0, -9).Resize(ActiveCell.CurrentRegion.Rows.Count, 1)

Dim criteria1 As String
criteria1 =  ActiveCell.Offset(0,-2).Value

If criteria1 = "" Then
    criteria1 = """"
End If

ActiveCell.FormulaR1C1 = "=SUMIFS(" & sumRange.Address & "," & criteriaRange.Address & "," & criteria1 & ")"

End Sub

 

[EggcellExperiments]

On the second to last line, getting
"Run-time error '1004':
Application-defined or object-defined error.

ActiveCell.FormulaR1C1 = "=SUMIFS(" & sumRange.Address & "," & criteriaRange.Address & "," & criteria1 & ")"