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

You should also mention which part is the original code and its main functionality, and which is your modification. It could ease on determining what the issue is. — Syakur Rahman, Nov 08 '17 at 01:00
Do any cells in your range contain a name other than "Aquino, Ervic"? This will exit the loop any time a cell contains anything other than "Aquino, Ervic". — E. Merckx, Nov 08 '17 at 01:14
The else statement and message box were the only parts I added. everything else was the original macro. My goal is to look for a name and pull their transactions for the month and format them. My original code worked fine however if their was no name (no transactions) it would debug and still format. I want it to display a message box that there is no transactions and stop.Yes their are many names. — Brian J., Nov 09 '17 at 02:36

score 3 · Answer 1 · answered Nov 08 '17 at 01:29

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.

YowE3K I get a Run-time error 438 Object doesn't support this property or method. — Brian J., Nov 09 '17 at 04:12
I typed in option explicit to see if all variables have been defined...it tells me variable not defined and it highlights this part a = — Brian J., Nov 10 '17 at 21:35
@BrianJ. Yes, you weren't declaring your variables in your posted code, so we couldn't tell whether you were letting them default to variants or whether they were Public variables declared outside that procedure. If you haven't declared `a` anywhere and you want to use `Option Explicit` (which is always a good idea) then add a `Dim a As Long, i As Long` to the code. But that doesn't explain an error 438. — YowE3K, Nov 10 '17 at 21:43
I ended up re-typing it with your suggestion and it works now. Probably had something that was copied on accident. Thank you very much. Works exactly the way I need it to! — Brian J., Nov 12 '17 at 17:49

If Else VBA code not working

1 Answers1