将VBA相同的规则应用于几行

Question

我有一个代码，我想应用于几行。我只为第 11 行创建，我的问题是我需要应用下面的代码直到第 60 行。我该怎么写？抱歉，VBA世界中仍然是新手，我很难理解For Each或循环规则。

Sub RectangleRoundedCorners11_Click()
    If Range("A11").Value = "new request" Then
        If Range("D11").Value = "" Or Range("E11").Value = "" Or Range("G11").Value = "" Or Range("H11").Value = "" Then
            MsgBox "Please fill all mandatory fields"
        End If
    End If
End Sub

Answer 1

下面的代码：

Sub RectangleRoundedCorners11_Click()
  for i=11 to 60
    If Range("A" & i).Value = "new request" Then
        If Range("D" & i).Value = "" Or Range("E" & i).Value = "" Or Range("G" & i).Value = "" Or Range("H" & i).Value = "" Then
            MsgBox "Please fill all mandatory fields"
        End If
    End If
  next i
End Sub

这将检查 11 到 60 之间的行。如果需要更多行，只需编辑 for 语句中的值即可。

Answer 2

您可以使用

此代码

Sub RectangleRoundedCorners11_Click()
        Dim col As Integer
        If Range("A11").Value = "new request" Then
            'loop from D to ...
            For col = 4 To 60
                If Range(Col2Letter(col) & "11").Value = "" Then
                    MsgBox "Please fill all mandatory fields"
                    Exit For
                End If
            Next
        End If
    End Sub
    Function Col2Letter(lngCol As Integer) As String
        Dim vArr
        vArr = Split(Cells(1, lngCol).Address(True, False), "$")
        Col2Letter = vArr(0)
    End Function

Answer 3

通过行

技巧

• 使用 VBA Option Explicit来检测错误。
• 在代码开头使用常量，以便能够在一个位置快速更改它们。
• 声明所有变量（例如 Dim i As Integer ）

《守则》

Option Explicit
Sub RectangleRoundedCorners11_Click()
    Const cFirst As Integer = 11  ' First Row
    Const cLast As Integer = 60   ' Last Row
    Const cRequest As String = "new request"                    ' Request Text
    Const cMsg As String = "Please fill all mandatory fields"   ' MsgBox Text
    Dim i As Integer
    For i = cFirst To cLast
        If Range("A" & i).Value = cRequest Then
            If Range("D" & i).Value = "" Or Range("E" & i).Value = "" _
                Or Range("G" & i).Value = "" Or Range("H" & i).Value = "" Then
                MsgBox cMsg
            End If
        End If
    Next
End Sub

• 可以使用范围或单元格创建一个单元格范围，例如A1：
  Range("A1")或Cells(1, "A")或Cells(1, 1) .
• If语句有几个版本。在这种情况下，其中两个是同样有效，简化如下：

---

    If x=y Then
        x=5
    End If
    ' or 
    If x=y Then x=5

更高级的版本

Sub RectangleRoundedCorners11_Click()
    Const cFirst As Integer = 11  ' First Row
    Const cLast As Integer = 60   ' Last Row
    Const cRequest As String = "new request"                    ' Request Text
    Const cMsg As String = "Please fill all mandatory fields"   ' MsgBox Text
    Const cColumns As String = "A,D,E,G,H"                      ' Columns List
    Dim vnt As Variant  ' Columns Array
    Dim i As Integer    ' Row Counter
    vnt = Split(cColumns, ",")  ' An array created with Split is 0-based.
    For i = cFirst To cLast
        If Cells(i, vnt(0)).Value = cRequest Then
            If Cells(i, vnt(1)).Value = "" Or Cells(i, vnt(2)).Value = "" _
                    Or Cells(i, vnt(3)).Value = "" _
                    Or Cells(i, vnt(4)).Value = "" Then MsgBox cMsg
        End If
    Next
End Sub