Erratic behaviour of .Find

When testing a subroutine, using exactly the same code, sometimes the .Find function works and sometimes it doesn't. Once it's stopped working it refuses to work again, but may have been fine numerous times beforehand. Advice as to what could be wrong would be much appreciated—I don't want to have to resort to the "For Each c in Range" method of finding what I'm looking for (it's bulletproof but much slower, of course).

In the code excerpt below the first Finds (and two similar others) work perfectly every time—they are used to find and set the ranges (rWk and rMon) to be searched. With the troublesome Find (searching for data in rWk), commenting out these initial Finds makes no difference, neither does adding variants (LookIn, LookAt, etc) as the experts say we should. The troublesome Find is simply looking for integers <=31 in a range of no more than 12 cells, but needs to do so hundreds of times during the routine so Find would be much more efficient.

Any help or advice much appreciated.

Please Login or Register  to view this content.

VBA beginner here, but have you tried declaring 's' as a Range instead of string?
- Moo

Try using some of the other arguments Find takes, not just what you are looking for.

as Norie said you need to use all the arguments. find remembers settings between uses and not just code uses-if you run a find in the excel ui those settings will affect your code too unless you use all the arguments

if you do not change the .Find line to include additional arguments you will continue to run into problems-you can make book on that :-)

additionally you don't have to activate

Please Login or Register  to view this content.

notice the periods before the Range calls.

and finally you should always test the result of a find to be sure it isn't Nothing before you try and use that range

Thanks, Joseph. I'm well aware of the need to include additional arguments and normally make a point of doing so, but while testing to overcome this problem they seemed either to make no difference or to make matters worse.

Speaking of which, neither the VBA Help nor the massive VBA book I have tells me what to use for the After:= argument. Presumably its an integer or long, so is "0" used to start the search at the first cell in the range?

I'm also aware that Activate is not essential, but having forgotten most of what I knew about VBA through years of disuse I need to watch what's happening. I write what I think will work and then deal with the problems. Yes, I know, not exactly competent, but it's like having to learn to walk all over again.

Originally Posted by JosephP

notice the periods before the Range calls.

Thanks for reminding me about that—easily overlooked.

Originally Posted by JosephP

and finally you should always test the result of a find to be sure it isn't Nothing before you try and use that range

Yep, it's in the code but I left it out of the example for simplicity's sake.

Thanks again for your help, I'm always grateful for advice.

Richard

hi, Richard,
the After argument is an optional Range object-one of the few arguments that you can usually leave out

For what it's worth you do not need to Activate the worksheet if you qualify everything properly.

For example here none of the Ranges are tied to 'Bills'.

Please Login or Register  to view this content.

To tie the ranges to the workbook only needs a little dot.

Please Login or Register  to view this content.

If you had qualified those ranges then the code might have worked.

JosephP reminded me about the missing full stops so I corrected that (and tried about a million other things), but it still made no difference. 'Activate' is only there for the purposes of debugging; once I no longer need to see what's happening they'll be removed.

In any event, I believe the revised arrangement using arrays to store the information instead of looking for it each time is a better method. Were I more expert I might have done it that way in the first place.