Consulting

Results 1 to 2 of 2

Thread: Autofilter List Exists

  1. #1
    VBAX Regular
    Joined
    Apr 2007
    Posts
    41
    Location

    Autofilter List Exists

    Good morning!

    I was wondering if there is any way to check if a number exists in the autofilter, in my code:

       If ActiveSheet.range("$A$2:$AF$458" ).AutoFilter(Field:=8, Criteria1:=Textbox1.text) = Null Then GoTo TheEnd
    the "Textbox1.text" could be any number. What I realise is if even the Textbox1.text assume a number that aren't in the autofilter the autofilter is turned on anyways and the cell that are writted are all hidden.

    If there are a way to check if the Textbox1.text number exists my macro will run faster because i'm using a loop to find information.


    Do Until Sheets("Folha1").range("H" & r).Value = ""
    ...
    Loop
    but the "If ActiveSheet.range("$A$2:$AF$458" ).AutoFilter(Field:=8, Criteria1:=Textbox1.text) = Null Then GoTo TheEnd" seems that aren't working.

    Anyone could help me?

    Best regards
    Microsoft 2010 | VBA 7.1

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello dzogchen,

    The Range find method will search the value. The code below ignores case and searches for the whole value. It will return the special object variable "Nothing" if the value is not found.
        Dim Rng As Range
    
    
            Set Rng = Range("$A$2:$AF$458").Columns(8).Cells
            If Rng.Find(TextBox1, , xlValues, xlWhole, xlByRows, xlNext, False, False, False) Is Nothing Then
                GoTo TheEnd
            End If
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •