Excel VBA - Sum function

Question

I'm trying to calculate the sum of my columns (column I). From 1 to the last record in I it has. When I record a macro I get this as output, but this is gibberish to me.

ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-4]C)"

I found another topic and there they said

LastRow = .Range("I" & .rows.Count).End(xlUp).row  
Range("I"&LastRow) = "SUM(I1:I...)"

Except in my case, I can't figure how to enter the lastrow of I in it. All help is welcome :)

Widor · Accepted Answer · 2012-02-22 13:22:19Z

3

There are two ways of referencing a cell - 'R1C1' and 'A1'. The former works like co-ordinates, with a relative number of rows (R) and cells (C).

The other reference style refers to the cell name on the sheet - B6, F67 etc.

Let's say you want to put your Sum() in cell B1 and LastRow has a value of 6:

ActiveSheet.Range("B1") = "=Sum(I1:I" & LastRow & ")"

Would insert the following function in cell B1:

=SUM(I1:I6)

answered Feb 22, 2012 at 13:22

Widor

13.3k7 gold badges47 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CustomX Over a year ago

Wow ... It seems it makes a big difference when you use spaces. FML

CustomX Over a year ago

I tried it like this Range("I" & LastRow) = "=SUM(I1:I"&LastRow&")"

Collectives™ on Stack Overflow

Excel VBA - Sum function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related