I want to Add "IF STATEMENT" If the Criteria Exist or Not on FilterMode

Question

I will like to add an If criteria when Last name is not found. If it is found then keep filter with criteria.

Sub Test()
    Dim PnrName As String
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Fltrrng As Range
    
    Firstrow = Range("B:B").Find("*", After:=Range("I1")).Row
    Lastrow = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    PnrName = InputBox("Please Enter Last Name")
    
    Set Fltrrng = ActiveSheet.Range("$I$" & Firstrow & ":$I$" & Lastrow)
    
    Fltrrng.AutoFilter Field:=9, Criteria1:="=" & PnrName & "*", Operator:=xlAnd
    
    '**If **PnrName is found keep filter**
    'Else
    'MsgBox "Last Name does not exist, Please Re-Enter Last Name"
    'End If**
End Sub

You know you do not have to use `AutoFilter` to check if a text is in a particular column or not? A simple `Application.WorksheetFunction.CountIf()` can tell you that and if the count is `>0` then you can use that in the `AutoFilter`. — Siddharth Rout, Oct 20 '20 at 17:39
Its Wrong, but I imagen somenthing like below? Count = Application.WorksheetFunction.CountIf(Range("$A$" & Firstrow & ":$A$" & Lastrow)) If Count > 0 Then Fltrrng.AutoFilter Field:=9, Criteria1:="=" & PnrName & "*", Operator:=xlAnd Else MsgBox ("Last Name " & PnrName & "Does not Exist") End If — Edgar Flores, Oct 20 '20 at 18:55
No. You are missing the 2nd argument. [WorksheetFunction.CountIf method (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.countif) Try this `Count = Application.WorksheetFunction.CountIf(Range("$A$" & Firstrow & ":$A$" & Lastrow), PnrName)` — Siddharth Rout, Oct 20 '20 at 19:00
BTW if `Field:=9` then not sure why are you searching in Col A? — Siddharth Rout, Oct 20 '20 at 19:02
I saw that error and I fix as well, yes, i was missing 2nd argument just 1 issue, i'm using Criteria1:="=" & PnrName & "*", Operator:=xlAnd and does not count if word its not compleat — Edgar Flores, Oct 20 '20 at 19:25
@SiddharthRout thank you for you help! I ended using "=" & PnrName & "*" on second argument :D — Edgar Flores, Oct 20 '20 at 19:39

score 0 · Answer 1 · edited Oct 20 '20 at 19:54

0

This code works for me now.

Sub Test()
    Dim PnrName As String
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Fltrrng As Range
    Dim Count As Long

    Firstrow = Range("I:I").Find("*", After:=Range("I1")).Row
      
    Lastrow = Range("A:A").Find("*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
      
    PnrName = InputBox("Please Enter Last Name")

    Set Fltrrng = ActiveSheet.Range("$I$" & Firstrow & ":$I$" & Lastrow)

    Count = Application.WorksheetFunction.CountIf(Fltrrng, "=" & PnrName & "*")
    
    If Count > 0 Then
        Fltrrng.AutoFilter Field:=9, Criteria1:="=" & PnrName & "*"
    Else
        MsgBox ("Last Name " & PnrName & " Does not Exist")
    End If
End Sub

edited Oct 20 '20 at 19:54

Siddharth Rout

147,039
17
206
250

answered Oct 20 '20 at 19:43

Edgar Flores

17
6

There are many more things that you need to take care of. **1.** When you are using `.Find("*"` to find the row then you have to be careful. You may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) **2.** For last row you can use `LastRow = Range("A" & Rows.Count).End(xlUp).Row`. This is also mentioned in that link – Siddharth Rout Oct 20 '20 at 19:53
**3.** You are using `InputBox`. What should happen when the user pressess cancel? You will need to handle that as well. **4.** You can skip the "=" in the `.CountIf` line so that it becomes `Count = Application.WorksheetFunction.CountIf(Fltrrng, PnrName & "*")` – Siddharth Rout Oct 20 '20 at 19:53

I want to Add "IF STATEMENT" If the Criteria Exist or Not on FilterMode

1 Answers1