CODEWITHSUNDEEP
vbaexcel
itzmurd4
itzmurd4

Reputation: 663

If Statement AutoFilter meets criteria

I am currently trying to make an If Statement that will only run if my list meets a certain criterion. However, right now the macro runs even though my AutoFilter criteria do not include 4 in Criteria1.

Would anyone know how to correct this issue?

Private Sub FilterAndPaste()    
  Sheets(1).Select

  If ActiveSheet.Range("$A$3:$M$807").AutoFilter(Field:=2, Criteria1:="4") Then


        ' Insert the VLOOKUP formula into the given range
        ActiveSheet.Range("M3").Offset(1, 0).Select
            ActiveCell.FormulaR1C1 = _
                "=VLOOKUP(RC[-9],'[TestFile.xlsm]Test'!C1:C13,12,0)"
            ActiveSheet.Range("M3").Offset(1, -1).Select
            Selection.End(xlDown).Offset(0, 1).Select
            Range(Selection, Selection.End(xlUp)).Select
                Selection.FillDown

        ActiveSheet.Range("A3").Offset(1, 0).Select
            ActiveSheet.Range(Selection, Selection.End(xlToRight).End(xlToRight).End(xlToRight).End(xlDown)).Select
            Selection.Copy

        Sheets("Example").Select
        ActiveSheet.Range("A4").Select
            ActiveSheet.Paste
            Rows("4:100").RowHeight = 12
    Else
        End
    End If

End Sub

Upvotes: 2

Views: 9028

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

I see two questions and I will try and answer them accordingly.

Q1. Right now the macro runs even though my AutoFilter criteria do not include 4 in Criteria1. Would anyone know how to correct this issue?

That is because the moment you say

If ActiveSheet.Range("$A$3:$M$807").AutoFilter(Field:=2, Criteria1:="4") Then

Excel will put an autofilter and your condition will be true.

Q2. I am currently trying to make an If Statement that will only run if my list meets a certain criterion.

To check for the criteria use this

With Sheets(1)
    If .AutoFilterMode = True And .FilterMode = True Then
        Debug.Print .Range("$A$3:$M$807").Parent.AutoFilter.Filters(2).Criteria1
    End If
End With

Something like this

With Sheets(1)
    If .AutoFilterMode = True And .FilterMode = True Then
        If .Range("$A$3:$M$807").Parent.AutoFilter.Filters(2).Criteria1 = "=4" Then
            '
            '~~> Rest of the code
            '
        End If
    End If
End With

Upvotes: 2

Related Questions