| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Syntax for stopping a Do Loop?
I have columns of data and recorded a macro for taking the average of every
seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Syntax for stopping a Do Loop?
Hi,
Post your recorded macro. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: I have columns of data and recorded a macro for taking the average of every seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Syntax for stopping a Do Loop?
See Exit Do statement in Do ...Loop in VBA Help!
-- Regards! Stefi €˛bondjel€¯ ezt Ć*rta: I have columns of data and recorded a macro for taking the average of every seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Syntax for stopping a Do Loop?
Mike,
Here it is: Do ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" ActiveCell.Offset(7, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" Loop "Mike H" wrote: Hi, Post your recorded macro. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: I have columns of data and recorded a macro for taking the average of every seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Syntax for stopping a Do Loop?
Hi,
there will no doubt be some comment that selecting cells isn't the best way to do what you want but if it works for you then so be it. This selects the cell then checks for data in the cell to the left and if it's empty the loop terminates Do ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" ActiveCell.Offset(7, 0).Range("A1").Select Loop Until ActiveCell.Offset(, -1).Value = "" -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: Mike, Here it is: Do ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" ActiveCell.Offset(7, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" Loop "Mike H" wrote: Hi, Post your recorded macro. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: I have columns of data and recorded a macro for taking the average of every seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Syntax for stopping a Do Loop?
Mike,
I only suggested using the blank cell because in my relative ignorance I thought it would be easiest. But I think your suggested solution worked. Thanks again. Jim "Mike H" wrote: Hi, there will no doubt be some comment that selecting cells isn't the best way to do what you want but if it works for you then so be it. This selects the cell then checks for data in the cell to the left and if it's empty the loop terminates Do ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" ActiveCell.Offset(7, 0).Range("A1").Select Loop Until ActiveCell.Offset(, -1).Value = "" -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: Mike, Here it is: Do ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" ActiveCell.Offset(7, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C[-1]:RC[-1])" Loop "Mike H" wrote: Hi, Post your recorded macro. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "bondjel" wrote: I have columns of data and recorded a macro for taking the average of every seven numerical entries. What syntax do I use to tell the macro to stop looping when it gets to a blank cell in the column? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stopping an Entry | Excel Discussion (Misc queries) | |||
| Find loop doesn't loop | Excel Discussion (Misc queries) | |||
| Stopping a CountIf | Excel Worksheet Functions | |||
| Stopping a formula | Excel Discussion (Misc queries) | |||
| Stopping Calculation | Excel Discussion (Misc queries) | |||
Linear Mode
