Get Last Row
 

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 dont 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

Get Last Row
 

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



"Jeremy" wrote:

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 dont 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

Get Last Row
 

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

"Tom Ogilvy" wrote:


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



"Jeremy" wrote:

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 dont 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

Get Last Row
 

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

Thanks

Jeremy

"Jeremy" wrote:

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

"Tom Ogilvy" wrote:


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



"Jeremy" wrote:

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 dont 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