If Else VBA code not working

Question

I am new to VBA and I usually google for pieces of code I need however this has proven to be difficult. I am trying to create a macro that searches for a specific name and copies and pastes all rows with that name in a separate sheet. This worked fine but I also want a message box to appear when the name is not there. I added some code and now it only shows the message box even if the name is actually there. Below is my code. Many thanks for any help or information.

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False

    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a

        If Worksheets("Sheet1").Cells(i, 1).Value = "Aquino, Ervic" Then

            Worksheets("Sheet1").Rows(i).Copy
            Worksheets("Ervic Aquino").Activate
            b = Worksheets("Ervic Aquino").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Ervic Aquino").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("sheet1").Activate

            Application.CutCopyMode = False

            Worksheets("Ervic Aquino").Activate
            Range("A1:K1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Application.CutCopyMode = False
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            Range("I2").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
            Range("H1").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(1).Select
            Selection.Font.Bold = True

            Dim LR As Long
            LR = Range("H" & Rows.Count).End(xlUp).Row
            Range("H" & LR + 1).Formula = "=SUM(H2:H" & LR & ")"

            Cells.Select
            Cells.EntireColumn.AutoFit
            Range("A2").Select

            'If there is no activity do nothing
        Else
            MsgBox "No Activity This Month"

            'End Loop
            Exit For

        End If

    Next

    Application.ScreenUpdating = True

End Sub

Answer 1

Your current code is displaying your "No Activity This Month" message if any row does not contain "Aquino, Ervic" but you only want a message to be displayed if none of the rows contain that string.

The easiest, and probably most efficient, way to do this is to perform the test first and then only process each row if an entry exists:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False

    If Application.CountIf(Worksheets("Sheet1").Columns(1), "Aquino, Ervic") = 0 Then
        MsgBox "No Activity This Month"
    Else
        a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

        For i = 2 To a
            If Worksheets("Sheet1").Cells(i, 1).Value = "Aquino, Ervic" Then
                Worksheets("Sheet1").Rows(i).Copy
                '...
                Cells.EntireColumn.AutoFit
                Range("A2").Select
            End If
        Next
    End If
    Application.ScreenUpdating = True
End Sub

---

I would also strongly recommend reading through the question How to avoid using Select in Excel VBA. Those Select and Activate statements will cause you so many problems in the future that it is better to invest some time now in learning how to get rid of them.