ExcelBanter - View Single Post

Ram

Thanks Richard

"Richard Buttrey" wrote:

The Cells( ) part of the code needs a row and column reference
separated by a "',".

The row reference is a number based on the total number of rows in the
object being addressed. In this case the Cells object is the whole
sheet, and the "Rows.Count" bit therefore returns the number 65536.

The "col' bit returns the constant you have declared. In this case
column "A". The bit about '<<== change to column, is simply a comment
(preceded by a ', and is telling you to change as appropriate for your
application.

So far the cells( ) gives us a reference to A65536.
The end(xlup) references the last row with a value in column A, and
the .Row bit on the end returns the row number of this row.

Hence Lrow1 equals (whatever the row number above:A)

HTH

On Fri, 21 Oct 2005 08:09:09 -0700, ram
wrote:

Thanks Les

it works great

Now I'm trying to understand why it works so that I can apply the code in
other circumstances.

Does Lrow1=Cells(Rows.Count,col).end(xlup).row find the last row with data?
if so why is it xlup and not xldown?

Does Const col As string= "A" <<== change to column determine the column to
look in first?
can you tell me what this does <<==change to column

Again thanks for your help

"Les Stout" wrote:

Hi Ram, sorry made a few errors with the last script, try this, it adds
row a & B and put the formula in C.

Sub InsertCalcE()
Dim rng As Range
Dim Lrow1 As Long
Dim CalcMode As Long
Const col As String = "A" '<<== CHANGE to column
Application.EnableEvents = False
Lrow1 = Cells(Rows.Count, col).End(xlUp).Row

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set rng = Range(col & "1:" & col & Lrow1) '<-Change to from row No.
With rng
.Offset(0, 2).FormulaR1C1 = "=SUM(RC[-2]+RC[-1])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________