Help! How to do a basic loop in VB?

[Stringsam]

I have worked extensively with Pivot Tables, etc in Excel (97version). But, the closest I have gotten to VB is using the "record" feature.

I now have a neat little Macro that performs several steps. But I need to be able to loop it until all the "assignment s" have been completed. I presume there is some simple feature to call up the Macro that I now have, and execute it until all the assignments have been made, stopping when the "To be Assigned" is zero?

If someone could write me out a simple command to launch, and stop my Macro it would be greatly appreciated.

Thanks for any help anyone can give me.

[sashi]

Hi there,

pls refer to below sample code segment.. take care..

for loop..

For index=start to end[Step step]
[statements]
[Exit For]
[statements]
Next[index] 
 

example usage of for loop

Dim d As Integer
For d = 0 To 2
System.Console.WriteLine("In the For Loop")
Next d
 

while loop..

While condition
[statements]
End While 
 

example usage of while loop..

Dim d, e As Integer
d = 0
e = 6
While e > 4
e -= 1
d += 1
End While
Debug.Print("The Loop ran " & e & "times")
 

do loop..

Do[{while | Until} condition]
[statements]
[Exit Do]
[statements]
Loop 
 

example usage of do loop..

Dim str As String
Do Until str = "Cool"
System.Console.WriteLine("What to do?")
str = System.Console.ReadLine()
Loop
 

[Stringsam]

I found similar code in a book yesterday, but I have failed at the posting. What I am trying to do is to have the Macro stop when the value in a cell reaches zero. That is, there is no one left to assign a dining spot to. I think I need to know how to reference the cell value, if it possible to do so from the Macro. Here is what I have tried, and got a syntax error message.

Sub Macro1_test_DoL oop()
'
' Macro1 Macro
' Macro recorded 8/6/2006
'
' The following is a "Recorded" Macro, which does work independently.
'
Application.Run "'NC - Table Assignments Calculations.xl s'!Assign_Diner s"
'
' The following cell will be at zero when the required cycles have been run.
'
x = [NC - Table Assignments Calculations.xl s]Table Compute!$R$8
Do Until x = 0
Loop
Msg Box x
End
End Sub

If anyone can offer a suggestion it would be appreciated. I am totally new at this, so I have no idea what is supposed to come first, or if I can even do it the way I am trying. Thanks.

[Stringsam]

Thank you sashi for the code!

[BSOB]

'i believe this is what you want.
'<cell value> is your "value in a cell"

do
if <cell value> = 0 then exit do
'<your macro loops here>
loop

it will go round and round until cell value = 0
also (more efficeintly) writen as:

do until <cell value> = 0
'<your macro loops here>
loop

[Stringsam]

'i believe this is what you want.
'<cell value> is your "value in a cell"

do
if <cell value> = 0 then exit do
'<your macro loops here>
loop

it will go round and round until cell value = 0
also (more efficeintly) writen as:

do until <cell value> = 0
'<your macro loops here>
loop

Thank you BSOB.

I have tried creating a simple exercise to test the Loop. The loop should stop when the cell value reaches zero. When I run the Macro, the steps work, but the "do until" does not recognize the "zero" when it is actually computed. Therefore, I have to shut the operation down. Amazingly though, if I hardcode the zero, the loop stops as desired. The "test" code follows. Maybe someone will have an idea. Thanks.
-----------------------------------------------------------------------------

Sub Test_Loop()
'
' Test_Loop Macro
' Macro recorded 8/8/2006
'

' Go to ActiveCell.Valu e which should become zero:
Sheets("Control Sheet").Select
Range("F2").Sel ect
' Do Until test:
Do Until ActiveCell.Valu e = 0
ActiveCell.Sele ct
' Now to execute Macro:
Sheets("Piv-Summary").Selec t
ActiveSheet.Piv otTables("Pivot Table2").PivotS elect "", xlDataOnly
ActiveSheet.Piv otTables("Pivot Table2").Refres hTable
Sheets("Pivot-detail").Select
Rows("3:3").Sel ect
Selection.Copy
Sheets("Assigne d").Select
Rows("100:100") .Select
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
' Sort
Columns("A: D").Select
Selection.Sort Key1:=Range("B2 "), Order1:=xlAscen ding, Header:=xlGuess , _
OrderCustom:=1, MatchCase:=Fals e, Orientation:=xl TopToBottom
' position cursor:
Range("c2").Sel ect
' End of recorded Macro
Loop
End Sub

[Stringsam]

I finally got it to work by going back to the "ActiveCell " at the end of the Macro as well as at the start. Maybe there is a better way, but it worked for me.

Thanks to all you who offered suggestions.