End Loop in Excel VBA

Question

I have a excel VBA for a workbook. How do I end a loop on the worksheets? I'm not sure on how to do that. Here is the code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Dim dtDate As Date
 Dim intHours As Long
 Dim ws As Worksheet

 intHours = 11
 dtDate = InputBox("Date", , Date)

 For Each ws In ThisWorkbook.Worksheets
 Set SelRange = Range("A6:A366")
 Next ws(**This where I need the loop to stop after the last worksheet**)

 For Each b In SelRange.Rows
 b.Value = dtDate + TimeSerial(intHours, 0, 0)
 b.Value = dtDate + TimeSerial(intHours, intMinutes, 0)
 intHours = intHours
 intMinutes = intMinutes + 1
 If intHours > 24 Then
        intHours = intHours - 24

End If
Next
End Sub

I need the loop to end after the last worksheet which is worksheet 6.

The loop will end at the last worksheet, but you are not using them correctly. What are you trying to do here?? The code has more than one problem.... — John Bustos
– John Bustos, Commented Dec 12, 2012 at 17:06
Doesn't the "For Each ws in ThisWorkbook.worksheets / Next ws" stop after the last worksheet? — rajah9
– rajah9, Commented Dec 12, 2012 at 17:06
If you need to exit before the last worksheet then you'd use Exit For — Tim Williams
– Tim Williams, Commented Dec 12, 2012 at 17:11
@JohnBustos I want the date prompt to come up when the workbook opens. That works...only problem is it will keep prompting me when I change worksheets...I only want it to prompt once when the workbook is open...What other problems do you see? — TankTank
– TankTank, Commented Dec 12, 2012 at 17:16
@TimWilliams at what part of the code would I apply that at? — TankTank
– TankTank, Commented Dec 12, 2012 at 17:19

Sorceri · Accepted Answer · 2012-12-12 19:30:49Z

1

Per your question you just need to check the worksheet index to see if it is 6 and if so then exit the for loop. See below. In regards to your comments; you need to change this to the on workbook open method to only run it once when the workbookis opened.

On a side note, your first FOR loop is out of the scope of the second FOR loop so you are just setting the range over and over and doing nothing with it until the first FOR loop quits. It may be better to explain what you are trying to accomplish over all so you get a better response.

Private Sub Workbook_Open()
Dim dtDate As Date
Dim intHours As Long
Dim ws As Worksheet

intHours = 11

For Each ws In ThisWorkbook.Worksheets
    'check the index of the worksheet and exit if it is 6
    If ws.Index = 6 Then
        Exit For
    End If
'get the date per sheet
dtDate = InputBox("Date", , Date)
    Set SelRange = Range("A6:A366")
Next ws '(**This where I need the loop to stop after the last worksheet**)

For Each b In SelRange.Rows
    b.Value = dtDate + TimeSerial(intHours, 0, 0)
    b.Value = dtDate + TimeSerial(intHours, intMinutes, 0)
    intHours = intHours
    intMinutes = intMinutes + 1
    If intHours > 24 Then
       intHours = intHours - 24
    End If
Next
End Sub

This is what I think you are looking to accomplish.

Private Sub Workbook_Open()
Dim dtDate As Date
Dim intHours As Long
Dim ws As Worksheet

intHours = 11

For Each ws In ThisWorkbook.Worksheets

dtDate = InputBox("Date", , Date)
    'check the index of the worksheet and exit if it is 6
    If ws.Index = 6 Then
        Exit For
    End If
    Set SelRange = ws.Range("A6:A366")
    For Each b In SelRange.Rows
        b.Value = dtDate + TimeSerial(intHours, 0, 0)
        b.Value = dtDate + TimeSerial(intHours, intMinutes, 0)
        intHours = intHours
        intMinutes = intMinutes + 1
        If intHours > 24 Then
           intHours = intHours - 24
        End If
    Next
Next ws '(**This where I need the loop to stop after the last worksheet**)


End Sub

edited Dec 12, 2012 at 19:30

answered Dec 12, 2012 at 17:36

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TankTank Over a year ago

Thanks for the help....You are correct I need to explain more...I would like the date to prompt for each sheet in this workbook...currently when I change it to Workbook_Open it only prompts for the first sheet...How do I get it to prompt for the others?

Sorceri Over a year ago

@TankTank You just need to move the input box inside the FOR loop and after the Sheet.Index check. I have updated the code to reflect this. I think you are still going to have an issue with your two FOR loops being seperated.

TankTank Over a year ago

you are right I am having problems with the FOR Loop being seperated...Thanks for the help.

Collectives™ on Stack Overflow

End Loop in Excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related