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.
Posts by DKB1983


I will attach again. Does it work now?

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
Display MoreSub 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

Thanks Fluff for your help.

I'm sorry. I thought it worked because it's giving me this:
=SUMIFS($K$148:$K$162,$J$148:$J$162,A)
But it's just a string. The function is not evaluating. I need that last argument to change from column to column, and I need the formula to return a value.

Ok. So I tried to use the same idea with .formular1c1. See my code and comments below:
Code
Display MoreSub rangetesting() Dim rng As Range Dim rng2 As Range Dim rng3 As Range Set rng = ActiveSheet.Range(ActiveSheet.Cells(147, 12), ActiveSheet.Cells(157, 16)) rng.Select Set rng2 = ActiveSheet.Range(rng.Cells(2, 2), rng.Cells(11, 5)) Set rng3 = ActiveSheet.Range("j148:k162") 'This formula works, but the r1c1 references M$1 of the sheet object. I want r1c1 of the rng object. rng2.FormulaR1C1 = "=r1c" 'By the same token I'm trying to reference cell R1C of rng, not R1C of the whole worksheet. rng2.FormulaR1C1 = "=sumifs(" & rng3.Columns(2).Address & "," & rng3.Columns(1).Address & "," & rng1.Range(r1c) & ")" End Sub
I feel like I'm so close!

Thank you both! That worked! Roy, I will research my questions first and if I still can't figure it out I will use the code button. Thanks again!

I'm trying to set the .formula object property using the sumifs formula. The last line is where I'm having trouble. I keep getting a type mismatch error. Can anyone shed some light on this for me? I'm new to vba.
Code
Display MoreSub rangetesting() Dim rng As Range Dim rng2 As Range Dim rng3 As Range Set rng = ActiveSheet.Range(ActiveSheet.Cells(147, 12), ActiveSheet.Cells(157, 16)) rng.Select Set rng2 = ActiveSheet.Range(rng.Cells(2, 2), rng.Cells(11, 5)) Set rng3 = ActiveSheet.Range("j148:k162") 'rng2.Formula = "=sum(" & rng3.Cells(1, 2) & "," & rng3.Cells(2, 2) & ")" rng2.Formula = "=sumifs(" & rng3.Columns(2) & "," & rng3.Columns(1) & "," & rng.Range("b$1") & ")" End Sub

I have a subroutine that uses the sumifs function. The final argument  R137C is a an absolute reference to a date so I can autofill down the column with each cell referencing R137C. However, if I insert a row somewhere above R137C I need my code to reference R138C because everything shifted down. How do I do this?
"=SumIfs(PunchList.xlsx!C12, PunchList.xlsx!C4, RC1, PunchList.xlsx!C8,R137C)"