SUM until last row using VBA to add formula in Excel

Question

I got problem with SUM. The idea is i want to use my own varible to SUM certain range.

This is my code

Range("B299").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-298]C:R[-1]C)"

i want to use lastrow variable to change the value of SUM

Here is my lastrow declaration, i create this variable on the other sub and i call it before i want to use SUM

lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

When i tried to run the macro, it showed :

run-time error '1004': ....

edited : i encountered another problem. It's about find lastrow function.

Let's say when i tried to run macro first time, it recorded lastrow as 300. But when i tried to run it second time, which is for example i have 200 row of data, the findlastrow function still recorded it as 300 instead of 200.

this is my findlastrow function

Sub FindLastRow()

lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

MsgBox "Last Row this sheet is " & lastrow

the lastrow variable, I declared it as global variable in Module1

Public lastrow As Integer

I wonder what's wrong with the code

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

Take a look at this post : Error in finding last used cell in VBA

So you should use this instead :

With ActiveSheet
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With
Range("B" & lastrow +1).FormulaR1C1 = "=SUM(R[-" & lastrow & "]C:R[-1]C)"

edited May 23 '17 at 10:33

Community

1
1

answered Nov 16 '15 at 10:30

R3uK

14,417
7
43
77

1

Or just: `"=SUM(R1C:R[-1]C)"` – Rory Nov 16 '15 at 12:08
@Rory : Indeed, I just keep the OP's format in my mind, but your solution is definitely better for that specific issue. ;) – R3uK Nov 16 '15 at 12:16
Hi R3uK, I want to say thank you for your answer. But now i encounter a new issue about findlastrow function. Can you help? The detail is in my first post, edited part. Thanks anyway :) – Randy Adhitama Nov 17 '15 at 00:42
@RandyAdhitama : First, you should Dim LastRow as `Long`, because integer is limited to (around) 32k so you are gonna overflow sometimes! Secondly, take a look at the link on the top of my answer (you'll see that the method with `xlCellTypeLastCell` or `UsedRange` are unreliable) you should use the method that is in my answer! ;) – R3uK Nov 17 '15 at 06:55
Thanks a lot R3uK. That answered all my problem. Sorry for the late upvote. Thanks again :d – Randy Adhitama Nov 18 '15 at 06:44

SUM until last row using VBA to add formula in Excel

1 Answers1