Posts by DKB1983

    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.

    I'm sorry. I thought it worked because it's giving me this:


    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:

    I feel like I'm so close!

    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.

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