Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Proper Do Loop Construction
I am trying to obtain the product of column A and B and put it into column
C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#2
|
|||
|
|||
Chas,
Get ready to hit yourself in the head! :) You have an Exit Do in your loop, right before it loops. Blew you right out of the water. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#3
|
|||
|
|||
"Earl Kiosterud" wrote: Chas, Get ready to hit yourself in the head! :) You have an Exit Do in your loop, right before it loops. Blew you right out of the water. -- Thanks for the fast answer appreciate it. It looks that way so I changed it and and put the "loop" statement before the" Exit Do" statement,when I run-it I get a compile error"Exit Do not within Do...Loop" Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#4
|
|||
|
|||
Chas,
You don't want the Exit Do statement inside your loop, and it doesn't belong outside a loop. Get rid of it. Sell it on Ebay. :) -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "ChasX" wrote in message ... "Earl Kiosterud" wrote: Chas, Get ready to hit yourself in the head! :) You have an Exit Do in your loop, right before it loops. Blew you right out of the water. -- Thanks for the fast answer appreciate it. It looks that way so I changed it and and put the "loop" statement before the" Exit Do" statement,when I run-it I get a compile error"Exit Do not within Do...Loop" Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#5
|
|||
|
|||
Hi
Exit do is used to step out of loop before the ending condition is filled. Like (this is an example only to demonstrate how it works): .... i=2 Do Until i=1000 Activesheet.Range("C" & i).Formula = "=A" & i & "*B" & i If ActiveSheet.Range("A" & i)=0 Then Exit Do i=i+1 Loop .... Btw. activating cells is time-consuming. Write formulas into cells by reference only. P.e. you code may be: .... i=0 Do Until ActiveCell.Offset(i,-2)="0" ActiveCell.Offset(i,0)="=RC[-2]*RC[-1]" i=i+1 Loop -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#6
|
|||
|
|||
The error says it perfectly.
That is because you don't need an Exit Do at all, it will exit when the loop condition is met. -- HTH Bob Phillips "ChasX" wrote in message ... "Earl Kiosterud" wrote: Chas, Get ready to hit yourself in the head! :) You have an Exit Do in your loop, right before it loops. Blew you right out of the water. -- Thanks for the fast answer appreciate it. It looks that way so I changed it and and put the "loop" statement before the" Exit Do" statement,when I run-it I get a compile error"Exit Do not within Do...Loop" Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
#7
|
|||
|
|||
Thanks everybody for your timely help!
Chas. "Arvi Laanemets" wrote: Hi Exit do is used to step out of loop before the ending condition is filled. Like (this is an example only to demonstrate how it works): .... i=2 Do Until i=1000 Activesheet.Range("C" & i).Formula = "=A" & i & "*B" & i If ActiveSheet.Range("A" & i)=0 Then Exit Do i=i+1 Loop .... Btw. activating cells is time-consuming. Write formulas into cells by reference only. P.e. you code may be: .... i=0 Do Until ActiveCell.Offset(i,-2)="0" ActiveCell.Offset(i,0)="=RC[-2]*RC[-1]" i=i+1 Loop -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "ChasX" wrote in message ... I am trying to obtain the product of column A and B and put it into column C(axb=value in column C). There are 100 values in column A and B. I set-up the following but it stops after product(it doesn't loop): Do Until ActiveCell.Offset(0,-2) = "0" ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" ActiveCell.Offset(1,0).Select Exit Do Loop Obviously I am new to VB and in need of assistance. W98SE,OfficeXP SBE. Thankyou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Printe Autofilter Criterias in a Loop | Excel Discussion (Misc queries) | |||
Using PROPER for Columns, rows or ENTIRE spreadsheet | Excel Worksheet Functions | |||
convert a column of text to proper | New Users to Excel |