Loop and fill Question
 

Hi,
I am looking for help on the following: I would like to know how to write a
macro that loops through a spreadsheet and determines the number of rows that
have data in columns A & B. After it knws how many rows have data, it will
than enter a formula in column C.

A B C
1 4
2 5
3 6

When I look through the posted questions and copy the information some
examples work for me others don't. I would really like to understand how this
code works.

Thanks again for all your help.

Ramone

Loop and fill Question
 

Hi Ram, i got the code below from this site and it works great.

Sub InsertCalcE()
Dim rng As Range
Dim Lrow1 As Long
Dim CalcMode As Long
Const col As String = "I" '<<== CHANGE to column A or B
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 & "4:" & col & Lrow1)'<-Change to column where
you want the formula e.g.3 for "C"
With rng
.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
TotalsE
End Sub
regards,

Les Stout

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

Loop and fill Question
 

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

Loop and fill Question
 

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

Loop and fill Question
 

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
__________________________

Loop and fill Question
 

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
__________________________