VBA to identify certain text in a data validation field

[CLoos]

I am trying to write an IF statement that looks at a data validation field ("A2") on a worksheet named "Select". If the data validation field contains the word "all" do nothing otherwise do something (turn autofilter). In searching posts I have not found the correct code to look at the data validation field and identify the text within it to work in the if statement. Can someone help me with this if statement?

[Norie]

Why not look at the value in the cell?

[Alf]

Perhaps something like

Please Login or Register  to view this content.

Alf

[CLoos]

trying to but the cell doesn't seem to be recognized

[CLoos]

Here is another one. Same issue. Sheet Select Range "A2" is equal to "All" but the code just keeps skipping to the Else statement. My guess is that it is not recognizing the value in the data validation list which is in cell "A2"
With Sheets("Billings Data")

.AutoFilterMode = False
.Range("BiData").AutoFilter
If InStr(Sheets("Select").Range("A2"), "All") Then
.Range("BiData").AutoFilter Field:=17
Else
.Range("BiData").AutoFilter Field:=17, Criteria1:=Sheets("Select").Range("A2")

End If
End With

[Norie]

Is it "All' or 'all'?

[CLoos]

I just figured this out. Finally! It is case sensitive and it is "ALL". Now it works. Thanks for your replies

[Alf]

If cell A2 only contains "ALL" or not then you don't need the InStr function as this finds a specified part of a string in a string. Instead you could use

Please Login or Register  to view this content.

Alf

[CLoos]

Yes, thank you I have already changed it. Boy did that silly thing stump me for two days! I appreciate your responses.