ActiveCell.Formula incorrect value

[khalid79m]

    Sheets("Total").Select

    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    Range("M4").Select
    ActiveCell.FormulaR1C1 = ("C1:C" & Lastrow)

i need the fomula to sum up c1:c & lastrow but this doesnt work, it just puts "C1:C4" into the cell

[Leith Ross]

Hello khalid79m,

You left out the SUM function, and using "C1 to last row" isn't R1C1 formula style. Stick with A1 style when writing macros. You rarely need to select cells when writing macros in VBA.

   Sheets("Total").Select

    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    Range("M4").Formula = "=SUM(C1:C" & Lastrow & ")"

Sincerely,
Leith Ross

[royUK]

Does this help?

Option Explicit

Sub sumIt()
'this will place the total of the cells in B into 4
    Dim rng    As Range
    Dim dSum   As Double
    Dim Lastrow As Long
    With Sheets("Total")
        Lastrow = .Cells(.Rows.Count, 2).End(xlUp)
        dSum = Application.WorksheetFunction.Sum(rng)
        .Cells(4, 13).Value = dSum
    End With
End Sub

[khalid79m]

Very useful . Thanks Mate