VBA Excel Formula with Dynamic Range And Variable

Question

I want to do a dynamic sum formula in VBA and it's some how very difficult for me because I don't use well integer variables. the last row might change in the future and I need that the range will be dynamic. thanks to those who will help me.

Sub SumColumns()
Sheets("data").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.Value = "sum"
Selection.Interior.ColorIndex = 33
Selection.Font.Bold = True

    Dim LastCol As Integer
    Dim LastRow As Integer
    With Sheets("data")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

Range("A1").End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[- " & LastRow & " + 1]C:R[-1]C)"
Selection.AutoFill Destination:=Range("B" & LastRow, "I" & LastRow), Type:=xlFillDefault
End Sub

that is the line with the error:

ActiveCell.FormulaR1C1 = "=SUM(R[- " & LastRow & " + 1]C:R[-1]C)"

SJR · Accepted Answer · 2017-10-23 10:19:32Z

1

Take the + 1 out of the quotes as that seems to be causing the problem and you need to deduct 1 otherwise you will be on row zero. The code below also removes your selects which are unnecessary and inefficient. And use your LastCol variable to determine across how many columns to copy the formula.

Sub SumColumns()

Dim LastCol As Long 'use Long rather than Integer
Dim LastRow As Long

With Sheets("data")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .Range("A" & LastRow + 1)
        .Value = "sum"
        .Interior.ColorIndex = 33
        .Font.Bold = True
    End With
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range("B" & LastRow + 1).Resize(, LastCol - 1).FormulaR1C1 = "=SUM(R[-" & LastRow - 1 & "]C:R[-1]C)"
End With

End Sub

edited Oct 23, 2017 at 10:19

answered Oct 23, 2017 at 10:07

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rafael Osipov Over a year ago

can you show me how to borderaround the last line of sum?

Rafael Osipov Over a year ago

sorry, gave it back what is fair is fair, used your code

SJR Over a year ago

You're entitled to award the answer to whoever you see fit. But really your last request was a separate question. FWIW I would use BorderAround - look it up in Excel Help.

shrivallabha.redij · Accepted Answer · 2017-10-23 10:42:28Z

1

You can get rid of many select portions and steam line code like below. Test it and see if this is what you are after.

    Sub SumColumns()
    Dim LastCol As Long
    Dim LastRow As Long
    With Sheets("data")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        With .Range("A" & LastRow).Offset(1, 0)
            .Value = "SUM"
            .Interior.ColorIndex = 33
            .Font.Bold = True
        End With
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A" & LastRow).Offset(0, 1).FormulaR1C1 = "=SUM(R[-" & LastRow - 1 & "]C:R[-1]C)"
        .Range("A" & LastRow).Offset(0, 1).AutoFill Destination:=.Range("B" & LastRow, .Cells(LastRow, LastCol)), Type:=xlFillDefault
        .Range("A" & LastRow, .Cells(LastRow, LastCol)).Borders.LineStyle = xlContinuous
        .Range("A" & LastRow, .Cells(LastRow, LastCol)).Borders.Weight = xlThin
    End With
    End Sub

edited Oct 23, 2017 at 10:42

answered Oct 23, 2017 at 10:15

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

2 Comments

Rafael Osipov Over a year ago

can you show me how to borderaround the last line of sum?

shrivallabha.redij Over a year ago

@RafaelOsipov I have edited the code. If it solves your issue then please mark this post as answer.

Collectives™ on Stack Overflow

VBA Excel Formula with Dynamic Range And Variable

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related