I have a table that I'm filling with the sumifs function. I'm trying to make it somewhat dynamic so that if a row get added or deleted above my table, the formula will still work. I've tried named ranges, I've tried various object.formula properties, etc. I think the issue is that I don't know how to reference an object from within the sumifs formula and still make the formula work. Below is my code. I'm attaching a worksheet with the macro embedded. The macro works on Sheet1 but not on Sheet2 because the table is located several rows down on Sheet2. Any help would be much appreciated.
Sub rangetesting() Dim rng As Range Dim rng2 As Range Dim startcell As Range ActiveSheet.Range("e1").Activate Do If ActiveCell.Value = "Start" Then Exit Do ActiveCell.Offset(1, 0).Activate Loop Set startcell = ActiveCell startcell.Offset(1, 1).Range("a1:d4").FormulaR1C1 = "=sumifs(R5C3:R39C3,R5C1:R39C1,RC5,R5C2:R39C2,R4C)" End Sub