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.

    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.

    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!