About Us

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
__________________________

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Fill handle fill down alternative methods question	Rufio	Excel Discussion (Misc queries)	1	May 18th 09 04:28 PM
Loop question	aelewis	Excel Discussion (Misc queries)	2	October 24th 07 08:12 PM
Setting up a simple loop to fill labels?	justchris[_2_]	Excel Programming	5	August 23rd 05 12:50 PM
another loop question	Patti[_5_]	Excel Programming	5	May 31st 04 07:43 AM
help on insert rows and fill down loop until end	mark mcgrath	Excel Programming	1	January 16th 04 01:50 AM

Menu

About Us