Posts by DKB1983

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.

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.

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

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:

Sub 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.

Sub 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)"