VBA formula not working well

Question

i'm programming the next formula in vba

ActiveSheet.Cells(ActiveCell.Row, 1).Select
ActiveCell.Offset(0, 7).Select

With ActiveCell
.formula = "=SUM(" & .Offset(1, 0).Address(0, 0) & ":" & _
.End(xlDown).Offset(0, 0).Address(0, 0) & ")"
 End With

and its causing me some problems, because if i run the macro once it just takes 1 row in the formula: =SUM(H11:H11), but if i run it twice it works perfectly, taking in the sum all the values until the first blank cell in the worksheet.

Can you help me to improve my macro so it works properly from the first run?

Thank you for your attention and in advance

best regards, Sergio

score 1 · Answer 1 · answered Jan 20 '14 at 16:03

1

Try this:

With ActiveCell
    lastrow = Cells(Rows.Count, .Column).End(xlUp).Row
    .Formula = "=SUM(" & .Offset(1, 0).Address & ":" & Cells(lastrow, .Column).Address & ")"
End With

answered Jan 20 '14 at 16:03

Dmitry Pavliv

35,333
13
79
80

1

yeah! i've tried this one as well, but the fact is that it looks for the last filled cell of the whole worksheet, which in my case is not working. but thanks for your answer anyway. it's good to learn new things everyday. i never thought that excel vba is such a powerful tool – Ruseiro Jan 21 '14 at 09:32

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

Is this what you are trying?

With ActiveCell
    .Formula = "=SUM(" & _
                     .Offset(1, 0).Address & ":" & _
                     .Offset(1, 0).End(xlDown).Address & _
                    ")"
End With

BTW INTERESTING READ

edited May 23 '17 at 11:56

Community

1
1

answered Jan 20 '14 at 16:04

Siddharth Rout

147,039
17
206
250

Thanks! that was what i was exactly looking for. So, for the record it was a problem in the arrangement of the declarations? – Ruseiro Jan 21 '14 at 09:23

VBA formula not working well

2 Answers2