CODEWITHSUNDEEP
vbaexcel
Randy Adhitama
Randy Adhitama

Reputation: 227

SUM until last row using VBA to add formula in Excel

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

Upvotes: 2

Views: 6229

Answers (1)

R3uK
R3uK

Reputation: 14537

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)"

Upvotes: 3

Related Questions