Excel loops and If blocks
 

Hi Team
I am able to get the call for various "runner" macros to meet the specific
number in a cell I have named Starters, but I am unable to put the right End
If or end with or end loop.
Can you see what statements I should include to make this macro continuous.

Sub Count()

Selection.End(xlDown).Select
ActiveCell().Select

Do Until ActiveCell = "Stop"

ActiveWorkbook.Names.Add Name:="Count1", RefersToR1C1:=ActiveCell
Selection.End(xlDown).Select
ActiveCell().Select
ActiveWorkbook.Names.Add Name:="Count2", RefersToR1C1:=ActiveCell
ActiveCell.Offset(0, 1).Select
ActiveCell().Select
ActiveWorkbook.Names.Add Name:="Starters", RefersToR1C1:=ActiveCell
ActiveCell.FormulaR1C1 = "=COUNTA(Count1:Count2)"
Range("Count1:Count2").Select
If "Starters" <= "7" Then
SixRunners
If "Starters" <= "8" Then
SevenRunners
If "Starters" = "15" Then
SixteenRunners


Range("Starters").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(2, -1).Select
ActiveCell().Select
Do Until ActiveCell = "Stop"
Loop
If ActiveCell = "Stop" Then Cells.Item(257).Select

End Sub
--
Thanks for any help.

Excel loops and If blocks
 

Hi,

you have the Do... and the Loop and following lines.

The Do should be at the start of the code you want to repeat and th
Loop at the end


eg:


Do until ...
lines of code
....
...
loop

hope this help

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=54443

Excel loops and If blocks
 

Why don't you start over and tell us what you are trying to do. It appears
you are making it harder than necessary.




--
Don Guillett
SalesAid Software

"Murph" wrote in message
...
Hi Team
I am able to get the call for various "runner" macros to meet the specific
number in a cell I have named Starters, but I am unable to put the right
End
If or end with or end loop.
Can you see what statements I should include to make this macro
continuous.

Sub Count()

Selection.End(xlDown).Select
ActiveCell().Select

Do Until ActiveCell = "Stop"

ActiveWorkbook.Names.Add Name:="Count1", RefersToR1C1:=ActiveCell
Selection.End(xlDown).Select
ActiveCell().Select
ActiveWorkbook.Names.Add Name:="Count2", RefersToR1C1:=ActiveCell
ActiveCell.Offset(0, 1).Select
ActiveCell().Select
ActiveWorkbook.Names.Add Name:="Starters", RefersToR1C1:=ActiveCell
ActiveCell.FormulaR1C1 = "=COUNTA(Count1:Count2)"
Range("Count1:Count2").Select
If "Starters" <= "7" Then
SixRunners
If "Starters" <= "8" Then
SevenRunners
If "Starters" = "15" Then
SixteenRunners


Range("Starters").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(2, -1).Select
ActiveCell().Select
Do Until ActiveCell = "Stop"
Loop
If ActiveCell = "Stop" Then Cells.Item(257).Select

End Sub
--
Thanks for any help.

Excel loops and If blocks
 

Thanks Tony
However when I take the second Do.. out the Break message is cannot do a
Loop without a Do so it is not seeing the first statement.
When I include the second do statement the break is a someting missing from
a block of If statements.
Obviously I will have to try to rethink what I am doing.
Once again thanks for your response. I will redo the macro on a simpler
sheet and try to frow it from there.
Murph

Excel loops and If blocks
 

I haven't looked at what your code is trying to do but a quick tidy u
suggests that this might be easier to read.

Do Until ActiveCell = "Stop"

ActiveWorkbook.Names.Add Name:="Count1", RefersToR1C1:=ActiveCell
Selection.End(xlDown).Select
ActiveWorkbook.Names.Add Name:="Count2", RefersToR1C1:=ActiveCell

ActiveCell.Offset(0, 1).Select
ActiveWorkbook.Names.Add Name:="Starters"
RefersToR1C1:=ActiveCell
ActiveCell.FormulaR1C1 = "=COUNTA(Count1:Count2)"

If Range("Starters") <= "7" Then SixRunners
If Range("Starters") <= "8" Then SevenRunners
If Range("Starters") = "15" Then SixteenRunners


Range("Starters").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

ActiveCell.Offset(2, -1).Select

Loop
Range("A2").Select

End Su

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=54443