Get Last Row

[Guest]

Dear All,

I've seen various postings regarding this subject but have not been able to
apply them to my application.

What I need to do is append the @Sum function to the last row of an Excel
sheet so it will sum the values in the columns. The spread sheet is filled
dynamically by means of an export from a Lotus Notes database.

Bob Phillips very kindly helped me do something similar for the last column
by doing as follows:

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

then add a formula to sum column 2 to that column like so

Cells(2, LastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"

How can I do the same for the last row? The above works great but I don't
understand why. I can see from help that the "FormulaR1C1" property "returns
or sets the formula for the object†but I don’t understand what the "RC"
stands for in the formula above nor what the [-1] indicates.

Any help would be gratefully received.

My apologies for my ignorance!

Jeremy

 

[Guest]

i = Range("B1").column

Lastrw= Cells(rows.count, i).End(xlup).row



Cells(lastrw + 1, i).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

C means the column containint the formula

C1 means Column A (Absolute)

C[1] means the column to the right of the column with the formula

R means the row with the formula

R6 means Row 6 (absolute)

R[-1] means the row above the row with the formula

R[1] would mean the row after the row with the formula

 

[Guest]

Hi Tom,

Sorry for the delay but I was free yesterday. This works great, thank you.
However, is it possible to specify a range, i.e. D19 to N19?

Jeremy

i = Range("B1").column

Lastrw= Cells(rows.count, i).End(xlup).row



Cells(lastrw + 1, i).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

C means the column containint the formula

C1 means Column A (Absolute)

C[1] means the column to the right of the column with the formula

R means the row with the formula

R6 means Row 6 (absolute)

R[-1] means the row above the row with the formula

R[1] would mean the row after the row with the formula

--
regards,
Tom Ogilvy

Dear All,

I've seen various postings regarding this subject but have not been able to
apply them to my application.

What I need to do is append the @Sum function to the last row of an Excel
sheet so it will sum the values in the columns. The spread sheet is filled
dynamically by means of an export from a Lotus Notes database.

Bob Phillips very kindly helped me do something similar for the last column
by doing as follows:

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

then add a formula to sum column 2 to that column like so

Cells(2, LastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"

How can I do the same for the last row? The above works great but I don't
understand why. I can see from help that the "FormulaR1C1" property "returns
or sets the formula for the object†but I don’t understand what the "RC"
stands for in the formula above nor what the [-1] indicates.

Any help would be gratefully received.

My apologies for my ignorance!

Jeremy

 

[Guest]

Sorry this wasn't very clear. I need to place the formula in every cell of a
specified range.

Thanks

Jeremy

Hi Tom,

Sorry for the delay but I was free yesterday. This works great, thank you.
However, is it possible to specify a range, i.e. D19 to N19?

Jeremy

i = Range("B1").column

Lastrw= Cells(rows.count, i).End(xlup).row



Cells(lastrw + 1, i).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

C means the column containint the formula

C1 means Column A (Absolute)

C[1] means the column to the right of the column with the formula

R means the row with the formula

R6 means Row 6 (absolute)

R[-1] means the row above the row with the formula

R[1] would mean the row after the row with the formula

--
regards,
Tom Ogilvy

Dear All,

I've seen various postings regarding this subject but have not been able to
apply them to my application.

What I need to do is append the @Sum function to the last row of an Excel
sheet so it will sum the values in the columns. The spread sheet is filled
dynamically by means of an export from a Lotus Notes database.

Bob Phillips very kindly helped me do something similar for the last column
by doing as follows:

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

then add a formula to sum column 2 to that column like so

Cells(2, LastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"

How can I do the same for the last row? The above works great but I don't
understand why. I can see from help that the "FormulaR1C1" property "returns
or sets the formula for the object†but I don’t understand what the "RC"
stands for in the formula above nor what the [-1] indicates.

Any help would be gratefully received.

My apologies for my ignorance!

Jeremy