Referencing ranges from within a formula

DKB1983 · May 27th 2020

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.

Code

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

Display More

**royUK** · May 27th 2020

I cannot open your file.

DKB1983 · May 27th 2020

I will attach again. Does it work now?

**royUK** · May 27th 2020

Yes that one opened.

I can't understand why you need to add the formulas using VBA.

DKB1983 · May 27th 2020

I'm automating a much larger process and have narrowed it down to the essential part that I need help with. If it's not possible I can live with that. It just seems like the solution would be simple.

**royUK** · May 28th 2020

I still don't understand what you mean about objects and formulas. Also, if you converted the data to a Table then formulas would auto populate as rows are added.

Overview of Excel Tables

Try this

Code

Sub Macro2()
'


Dim lRw As Long


lRw = Cells(Rows.Count, 5).End(xlUp).Row
Range("F5:I" & lRw).FormulaR1C1 = "=SUMIFS(R5C3:R39C3,R5C1:R39C1,RC5,R5C2:R39C2,R4C)"
End Sub

Referencing ranges from within a formula

Participate now!

Share

Tags