Unable to AutoSum with LastCol in formula

Im having an issue writing VBA to Autosum where columns may increase or decrease from time to time. Take below as an example. I have set my LastCol to find the last column, i then want to autosum from column B on the row to the last column to get my 'Total. I want to avoid R1C1 Formulas where possible. Also the RC[-4] will change depending how many columns are on spreadsheet.

Sub AutoSum()

    Dim LastCol As Integer

    Sheets("Sheet1").Select
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    Cells(2, LastCol1 + 1).Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,RC[-4]: RC[-1])"

End Sub

Answers


Give this a shot:

Sub AutoSum()

    Dim LastCol As Integer

    With Sheets("Sheet1")

        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Cells(2, LastCol1 + 1).Formula = "=SUBTOTAL(9,Offset(B2,0,0,1," & LastCol-1 & "))"

    End With

End Sub

After running above code:


Need Your Help

Apache Karaf Rest Service Issue

java osgi apache-karaf

I am having a WAB application which is just having only one html file, and its working fine. the code is available on the below git link

Error in Fragment

android android-fragments

I'm writing programs with a simple Fragment and I got an error as below: