Excel中的宏控制周期错误 [英] Wrong cycle in macro control in excel

问题描述

我有这个宏

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim x As Integer
Dim S1 As String
Dim S2 As String


S1 = "Football"
S2 = "Basket"
x = 1
    Do
        If IsEmpty(Cells(x, 5)) And ((Cells(x, 3) = S1) Or (Cells(x, 3) = S2)) Then
            MsgBox "Insert a value in the empty cell"
            Cancel = True
        End If
        x = x + 1

    Loop Until Cells(x, 1) = ""


End Sub

当第5列为空并且第3列包含 Football 或 Basket 时，我单击"x"按钮关闭工作表时，宏将进行控制并显示一个消息框，警告您已插入一个值.检查通过了，但我不知道 MsgBox 出现了16次而不是1次.为什么?

When i click the "x" button to close the sheet if the column 5 is empty and the 3 column contains Football or Basket the macro makes a control and appear a Message box to alert that you have insert a value. The check goes but i don't know the MsgBox appears 16 times and not 1. Why?

推荐答案

将我的评论放入答案中.还要添加更多东西.

Putting my comments into an answer. Adding more things as well.

1. 声明您的变量/对象.您将不太容易出错.如果要在Excel中处理行，最好将它们声明为 LONG
2. 完全限定您的对象.例如，哪个单元格在哪个工作表中?如果要在关闭工作簿时检查 sheet1 中的单元格，但 sheet2 处于活动状态，则将无法获得所需的结果
3. 随着循环不断进行，直到找到所有匹配项，您将收到多条消息.第一场比赛后退出循环
4. 使您在 MsgBox 中的消息更有意义.用户将如何知道哪个单元格为空:)

1. Declare your variables/objects. You will be less prone to errors. If you are working with rows in Excel, it is better to declare them as LONG
2. Fully qualify your objects. For example which cells and in which sheet? If you want to check the cells in sheet1 but sheet2 is active when you are closing the workbook then you will not get the desired results
3. You are getting multiple messages as the loop keeps on going till it finds all matches. Exit the loop after the first match
4. Make your Message in the MsgBox more meaningful. How will the user know which cell is empty :)

这是您要尝试的吗?(未测试)

Is this what you are trying? (UNTESTED)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim S1 As String, S2 As String
    Dim lRow As Long, i As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    S1 = "Football": S2 = "Basket"

    With ws
        '~~> Find the last row which has data
        '~~> we will loop till there
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If Len(Trim(.Range("E" & i).Value)) = 0 Then
                Select Case .Range("C" & i).Value
                    Case S1, S2
                        '~~> Tell user which cell is empty
                        MsgBox "Insert a value in the cell " & _
                        .Range("E" & i).Address

                        Cancel = True

                        '~~> Exit the loop after the first match
                        Exit For
                End Select
            End If
        Next i
    End With
End Sub

这篇关于Excel中的宏控制周期错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！