PDA

View Full Version : Solved: Need help with a simple loop.



Cartwheels
12-11-2008, 10:20 AM
I need some help with what would seem like a simple simple loop. What I would like to do is start on sheet 3 do some stuff, go to next sheet, do same stuff, go to next sheet... ect till there are no more. I cannot seem to end it correctly.
Here is what I have:

Sheets("Sheet3").Select
Do
ActiveSheet.Next.Select
'do some stuff
Loop Until ??????
End Sub

Btw, is there a reference sheet on the web that can help with what is ok after loop until?

nst1107
12-11-2008, 10:39 AM
The Excel help files do a good job of explaining Do...Loop statements. If you're using 2007, make sure you are searching Developer Reference. Does ActiveSheet.Next.Select work?

MaximS
12-11-2008, 10:44 AM
you can try to put something like that into your Sub:


Dim i as Integer

For i = 1 to 10
Sheets(i).Select
'Do some stuff
Next i

Bob Phillips
12-11-2008, 10:46 AM
For i = 3 To Worksheets.Count

With Worksheets(i)

'do some stuff
End With
Next i

Cartwheels
12-11-2008, 12:40 PM
Thanks for the replies. I still am having some trouble though. I a little bit of a newb, so go easy on me.

Yes, this part does work, ActiveSheet.Next.Select. it is just the until part I end up with an error on.

Let me explain a little more to see if that helps.

I have at least three sheets but don't know how many all together, it will vary.
To keep things simple. lets say I want to clear (A1) starting on sheet 3
and repeating through all the sheets that follow. Till the end.

Thanks again

Bob Phillips
12-11-2008, 12:59 PM
Don't do it that way, we have shown you better ways.

CreganTur
12-11-2008, 01:01 PM
If you want to understand the basics of loops you can take a look at the article I wrote- just click on the Articles link under my name.

Cartwheels
12-11-2008, 01:55 PM
For i = 3 To Worksheets.Count

With Worksheets(i)

'do some stuff
End With
Next i



Ahhhhhhh, I finally got it. I kept running into a bug and missed the fact I was missing .select on line 2.
In other words it should be

For i = 3 To Worksheets.Count

With Worksheets(i).select

'do some stuff
End With
Next i


Btw, I love this forum, I have a feeling I'm going to learn a lot here!

Bob Phillips
12-11-2008, 02:31 PM
No it shouldn't! Selecting is inefficient. Work via the referenced worksheet object
, not a selected worksheet.

mdmackillop
12-11-2008, 03:24 PM
i.e.
For i = 3 To Worksheets.Count
With Worksheets(i)
.Range("A1").ClearContents
End With
Next i

Cartwheels
12-11-2008, 03:30 PM
Edit: I posted just after you md. I bet that is what I'm missing.

Cartwheels
12-11-2008, 03:38 PM
That is it! Thank you everyone!

Cartwheels
12-12-2008, 01:08 PM
I know I marked this as solved , but I wanted to see if you guys could help me with one more little thing I need to finish this macro up.

If I wanted to have cell (a5) selected on these sheets when finished what would I need to write in?


For i = 3 To Worksheets.Count
With Worksheets(i)
.Range("A1").ClearContents
Activate (A5) Here on these sheets
End With
Next i


I'm having trouble learning this type of code! Is this VBScript and where can I go to learn this?

lucas
12-12-2008, 01:19 PM
You have to reference the range just as you did with A1.

.range("A5").select

activate is the wrong action.....

Also as Maxim shows in post #3 you should define the variabe i

If you used Option explicit at the top of your module as you should always do, you would have recieved an error if you did not do this....

lucas
12-12-2008, 01:21 PM
Actually select will not work either.....what are you doing with A5?

Cartwheels
12-12-2008, 01:25 PM
I want to exit the macro with those cells activated if possible. In other words that would be the finishing touch.

lucas
12-12-2008, 02:05 PM
I believe the only way you can do that is to select each sheet which is not the most efficient way to go.

Option Explicit
Sub a()
Dim i As Long
For i = 3 To Worksheets.Count
Sheets(i).Select
With ActiveSheet
.Range("A1").ClearContents
.Range("A5").Select
End With
Next i
End Sub



Unless it is absolutely necessary I would avoid this step and stick with the code provided above.

mdmackillop
12-12-2008, 02:16 PM
If it is appropriate, you can set Excel to go to a specific range when a sheet is activated. This code goes into the WorkBook module.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Sheet1"
Range("A5").Select

Case "Sheet2"
Range("B5").Select

End Select
End Sub