0

I have a macro for which I would like that it calculates me the sum of all my value contains in column B (from cell B2 to last row). I configured my macro in order that it selects the first cell empty just under the lastrow of my column B (until the penultimate line of my VBA code, my macro works perfectly, I tested it).

My problem is that the last line of my VBA code does not work, apparently the last line of my VBA code: ActiveCell.Formula "=SUM(B2:B" & lastrow & ")" is not correct and because of this line, the macro returns me the error message

Compile error: invalid use or property.

If someone could help me, that would be great. Thanks a lot in advance. Xavi

Sub Process()   
    Dim lastrow As Long

    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
    ActiveSheet.Cells(ActiveSheet.Rows.Count,Selection.Column).End(xlUp).Select
    ActiveCell.Offset(1, 1).Select

    ActiveCell.Formula "=SUM(B2:B" & lastrow & ")"
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Xavi
  • 207
  • 1
  • 3
  • 20
  • 3
    You're missing the equals sign: `ActiveCell.Formula = "=SUM(B2:B" & lastRow & ")"` – BigBen Jan 16 '19 at 15:50
  • 2
    Please read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code fast and reliable. – Pᴇʜ Jan 16 '19 at 15:52
  • Another alternative is to use dynamic named ranges, if you don't mind having your SUM() function in a cell other than the one right below your column. Define a Named Range (e.g. SumRange) and then copy the following into the Refers to box `=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)` ... then enter `=SUM(SumRange)` in the cell you want the sum to appear. Anything you add to the end of the range in column B will automatically be included in your sum. Note that no gaps are allowed in column B. – MBB70 Jan 16 '19 at 16:15
  • @MBB70 worth to mention that `Offset` is volatile, so the sum is recalculated on *any* calculation Excel performs not only on calculations that the sum depends on. – Pᴇʜ Jan 16 '19 at 16:20
  • agreed on the inefficiency issue...this method is however handy nonetheless so I thought I'd share. – MBB70 Jan 16 '19 at 16:26
  • thanks a lot to all of you for your help! – Xavi Jan 16 '19 at 16:35

0 Answers0