vba Excel VBA中的结束循环

End Loop in Excel VBA

提问by TankTank

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.

我有一个用于工作簿的 Excel VBA。如何结束工作表上的循环？我不确定如何做到这一点。这是代码。

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.

我需要循环在最后一个工作表（即工作表 6）之后结束。

采纳答案by Sorceri

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.

根据您的问题，您只需要检查工作表索引以查看它是否为 6，如果是，则退出 for 循环。见下文。关于您的意见；您需要将其更改为 on workbook open 方法，以便仅在打开工作簿时运行一次。

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.

附带说明一下，您的第一个 FOR 循环超出了第二个 FOR 循环的范围，因此您只需一遍又一遍地设置范围，并且在第一个 FOR 循环退出之前什么都不做。最好解释一下您要完成的工作，以便您得到更好的回应。

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