Repeating an IF statement with multiple variables

Question

I have a macro that adds 1 to a cell in column 53 (column BA) if the row below has a cell containing the number “(2)” enclosed in brackets and another cell containing the word “Adult”.

This happens as follows:

Sub BUBFindAdults2()

lastRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

For x = 3 To lastRow

  If InStr(1, Sheets("Sheet1").Cells(x, 3), "(2)") <> 0 _
  And InStr(1, Sheets("Sheet1").Cells(x, 31), "Adult") <> 0 Then
    Sheets("Sheet1").Cells(x - 1, 53).Value = _
    Sheets("Sheet1").Cells(x - 1, 53).Value + 1
  End If

Next x

End Sub

However, I also need to add 1 to the same cell if the two lines contain "(3)" and "Adult". And if the lines three below contain "(4)" and "Adult". Etc. You see the pattern!

So far I have circumvented this by simply repeating the same code as follows:

Sub BUBFindAdults2()

lastRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

For x = 3 To lastRow

  If InStr(1, Sheets("Sheet1").Cells(x, 3), "(2)") <> 0 _
  And InStr(1, Sheets("Sheet1").Cells(x, 31), "Adult") <> 0 Then
    Sheets("Sheet1").Cells(x - 1, 53).Value = _
    Sheets("Sheet1").Cells(x - 1, 53).Value + 1
  End If

  If InStr(1, Sheets("Sheet1").Cells(x, 3), "(3)") <> 0 _
  And InStr(1, Sheets("Sheet1").Cells(x, 31), "Adult") <> 0 Then
    Sheets("Sheet1").Cells(x - 2, 53).Value = _
    Sheets("Sheet1").Cells(x - 2, 53).Value + 1
  End If

  If InStr(1, Sheets("Sheet1").Cells(x, 3), "(4)") <> 0 _
  And InStr(1, Sheets("Sheet1").Cells(x, 31), "Adult") <> 0 Then
    Sheets("Sheet1").Cells(x - 3, 53).Value = _
    Sheets("Sheet1").Cells(x - 3, 53).Value + 1
  End If

Next x

End Sub

, , , 10+! , VBA . , , .

.

Answer 1

, . . , 10 , :

Sub BUBFindAdults2()

lastRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

For x = 3 To lastRow     
  If InStr(1, Sheets("Sheet1").Cells(x, 31), "Adult") <> 0 Then      
    For i = 1 To 10
      If InStr(1, Sheets("Sheet1").Cells(x, 3), "(" & (i+1) & ")") <> 0 Then
        Sheets("Sheet1").Cells(x - i, 53).Value = _
        Sheets("Sheet1").Cells(x - i, 53).Value + 1
      End If
    Next i
  End If
Next x

End Sub

Answer 2

31, , "Adult". - , , .

Sub BUBFindAdults2()
    With Sheets("Sheet1").UsedRange
       .AutoFilter 31, "*Adult*"
       Dim r As Range, i As Integer
       For Each r In .SpecialCells(xlCellTypeVisible).EntireRow
         For i = 2 To 4
            If r.Cells(3) Like "*(" & i & ")*" Then
                With r.Offset(1 - i).Cells(53)
                    .Value = .Value + 1
                End With
            End If
         Next
       Next
      .Parent.AutoFilterMode = False
  End With
End Sub