Autofilter and offset issue

[izzy117]

I am trying to filter data in column A. If I find a match I plan on selecting that match and if no match I want to offset to a blank cell below the list. The list contains no duplicates and never will/should.

'Fruitforsale = Pears
 
Range("A5").select
Selection.AutoFilter Field:=1, Criteria1:=(Range("Fruitforsale"))
Range("A65536").End(xlUp).Select
If ActiveCell.Value <> Range("Fruitforsale").Value Then
ActiveCell.Offset(1).Select
End If
 
'Example
 
A5 Header
apples
oranges
grapes
bananas
 
A10 should be selected but it actually selects A6.

Please advise?



Totaly confusing when I see evidence that this macro worked fine two days ago with no changes made (psw protected). As proof to me the rows with oranges, grapes and bananas only exist because this macro did function correctly in the creation of those rows.
I do not use a Find macro because I have found it has issues giving me an exact match between "pear" and "pears" and is case sensitive.

Note the data I am filtering is 1,600 rows long, not sure if that affects the autofilter function.
Running Office/Excel 2000, Windows 2000. All items updated Feb 27th.

 

[RoryA]

Find will work just fine as long as you specify Lookat:=xlWhole and MatchCase:=False

 

[izzy117]

Find((Range("Fruitforsale").Value), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

Much better, more reliable. TYVM rorya