Entering a Formula via VBA for a Variable Range

    I have a formula [=SUMIFS(Worksheet!R2C6:R50000C6,Worksheet!R2C9:R50000C9,RC1,Worksheet!R2C4:R50000C4,R13C,Worksheet!R2C1:R50000C1,R2C4)] that I need to add to every cell within a variable range based on which columns are populated in row 13. The range always starts at G14 but it can go on to multiple columns and rows.


    I know I can find the last column using this:


    Dim LastCol As Long
    LastCol = Cells(13, Columns.Count).End(xlToLeft).Column


    And I can get the last row using this:


    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row


    But what I can't figure out is how to get the formula into LastRow:LastCol range. Does that make sense?


    Thanks for any help!

    Re: Entering a Formula via VBA for a Variable Range


    With VBA if you have a formula that you can write for a certain cell, let's say A1 and that formula is written so that if you copied it from A1 and pasted it into another cell and it changes the references, then you can with VBA just put that formula into all the cells with A1 being the first cell and it will copy automatically.


    So for example if I have VBA write a formula for cell A1 that basically says =B1+C1, then that formula can be written into A1 to A5 and it will copy correctly into all those cells.


    The code for such an example would look like this.

    Code
    With Range("A1:A5")
    .Formula = "=B1+C1"
End With


    So if your formula is written so that if it was put into G14 and then copied to all the other cells it would be correct, then you can write the formula to all the cells at once.


    It looks like your formula is all absolute ranges so I'm not sure if I'm on the right track.


    Code
    With Range("G14", Cells(lastrow, lastcol))
    .FormulaR1C1 = "=SUMIFS(Worksheet!R2C6:R50000C6,Worksheet!R2C9:R50000C9,RC1,Worksheet!R2C4:R50000C4,R13C,Worksheet!R2C1:R50000C1,R2C4)"
End With

    Re: Entering a Formula via VBA for a Variable Range


    Thank you, skywriter! It's the With Range("G14", Cells(lastrow, lastcol)) that I was missing. So simple. Thank you for your brilliance!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login