Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill handle fill down alternative methods question | Excel Discussion (Misc queries) | |||
Loop question | Excel Discussion (Misc queries) | |||
Setting up a simple loop to fill labels? | Excel Programming | |||
another loop question | Excel Programming | |||
help on insert rows and fill down loop until end | Excel Programming |