Applying VBA the same rule to several rows

Question

I have a code that I want to apply to several rows. I only created for Row 11, my problem is that I need to apply below code until Row 60. How can I write it? Sorry still new in VBA world & I am having hard time understanding the For Each or looping rule.

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

If you still want VBA, you can use `Worksheet_Change` Event with `Intersect`. You may want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Dec 28 '18 at 09:59
really appreciate the input & taking time to answer my question. — Lois, Dec 28 '18 at 10:56

score 1 · Answer 1 · answered Dec 28 '18 at 10:02

Code below:

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

This will check the rows between 11 and 60. If you need more rows, just edit the values in the for statement.

really thank you, you code made it really simple and easy to understand, — Lois, Dec 28 '18 at 10:56

score 1 · Answer 2 · answered Dec 28 '18 at 10:05

You can use this code

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

Thank you for taking time to provide answer, really appreciate it a lot. — Lois, Dec 28 '18 at 10:55

VBasic2008 · Accepted Answer · 2018-12-28T11:12:00.770

Through Rows

Tips

Use Option Explicit for VBA to detect errors.

Use constants at the beginning of the code to quickly be able to change them in one place.

Declare all variables (e.g. Dim i As Integer)

The Code

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

A one cell range can be created using Range or Cells e.g. for A1:
Range("A1") or Cells(1, "A") or Cells(1, 1).
The If statement has a few versions. In this case two of them are equally valid, simplified as follows:

    If x=y Then
        x=5
    End If

    ' or 

    If x=y Then x=5

A More Advanced Version

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

Applying VBA the same rule to several rows

3 Answers3

Through Rows

Tips

The Code

A More Advanced Version