Solved: Find All function works Intermittantly

[Rob342]

I knew i would be back with this one from a previous post
I am using Chip Pearsons routine to find all occurences on a sheet

When i open up the form and go to that tab and run it it does not find anything at all, if i go back and clear Col T on the MainDB sheet then reinput "I" in that column then it works ok, But........... as soon as i close the WB down then reopen it go to that tab and run the routine it comes up with nothing again!!!!!!

Does anybody have any idea why it is doing this or am i missing something?
Have tried all the obvious (Trim) etc but nothing seems to work
Enclosed a sample workbook but this works every time??

Rob

[Kenneth Hobs]

I don't see a problem. If you run it an that sheet is not the active one, then nothing is found.

[Rob342]

Hi Kenneth
Thanks for looking
I have added .activate to the routine and now it works all the time

I always thought by using set ws=worksheets("Sheet1") along with
With & End With, automatically activates the sheet?

Rob

[snb]

That's a lot of code to perform a simple action:

[vba]
Sub snb()
With Sheets("MainDB").Cells(1).CurrentRegion.Columns(20)
.AutoFilter 1, "I"
MsgBox .Offset(1).SpecialCells(12).Address
.AutoFilter
End With
End Sub

[/vba]

PS. Although you used With... End with , you forgot to add dots before the Range.
Read some more on With ... End With in the VBA helpfiles.

[Kenneth Hobs]

As snb showed, Activate and Select are usually not needed. This usually makes your macros faster.

[Rob342]

Kenneth & snb

Thanks for help both most appreciated

Rob