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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.