PDA

View Full Version : Solved: Find All function works Intermittantly



Rob342
08-25-2012, 11:06 AM
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
08-26-2012, 02:51 PM
I don't see a problem. If you run it an that sheet is not the active one, then nothing is found.

Rob342
08-27-2012, 02:29 AM
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
08-27-2012, 03:35 AM
That's a lot of code to perform a simple action:


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



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
08-27-2012, 07:09 AM
As snb showed, Activate and Select are usually not needed. This usually makes your macros faster.

Rob342
08-27-2012, 07:52 AM
Kenneth & snb

Thanks for help both most appreciated

Rob