Autofilter List Exists

[dzogchen]

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

[Leith Ross]

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