CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

VBA Excel Formula with Dynamic Range And Variable

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

Upvotes: 0

Views: 4454

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

Upvotes: 1

SJR
SJR

Reputation: 23081

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

Upvotes: 1

Related Questions