Writing a Find Function

[Ramses505]

I was trying to produce a function which would search a specified Range for a passed value; declared like this;

Please Login or Register  to view this content.

My Workbook has multiple sheets and the named Ranges ActiveStaff and StaffList are not on the same sheet from where the Function will be called.

I just need to get either True or False as to whether the passed name exists on the list indicated by the pFlag switch.

If the name exists (on the pFlag List), the function returns "1" correctly (there is currently only 1 instance of each name on each list). However, if I call the function and the name is not on the list indicated I get an error which says "Object variable or with block variable not set"

I have tried better qualifying the named ranges like this;

Please Login or Register  to view this content.

and this;

Please Login or Register  to view this content.

but the results are the same.

While I would like to fix the problem I would also like to understand whats happening better as I would probably like to create my own function that allows me to pass both the search string and the range to search in the end.

Thanks for reading.

[Greg M]

Hi there,

Please Login or Register  to view this content.

In the above code, rng will have a value of Nothing if the required string value was not contained in the range being searched, so attempting to count the number of cells it contains (rng.Count) will produce an error.


The following version of your code allows for the situation where the required value is not located, and returns a value of zero in such a case.

As you mentioned in your post, it is always better to use fully-qualified definitions in your code.

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Ramses505]

Hi Greg, Thanks so much for taking the time read and post a reply, appreciated.

So, yes, your adjustments work and produce the results I expect, thats great.

Interesting code layout, I have not seen that way of writing an If before, but my coding expertise is in other languages. Might give it a try as it saves a little space.

I note that even if I take the qualification out it still works as expected. I agree it's generally best to fully qualify things although I am not a huge fan (yet ?) of the dot syntax that vba uses - seems to produce long lines, at least to my eye.

I have to accept that counting the ranges in a range with no entries does not produce "0". but I don't like it -

Thanks again, getting help is so ... helpful.

[Greg M]

Hi again,

Many thanks for your very prompt feedback.

You're welcome - glad I was able to help.

Ok on your comments about code layout etc. - I've been dealing almost exclusively with VBA (Excel and Access) for quite a while now, so I'm not very familiar with other code layouts. I suppose it's like most things in life - what you're used to is good, and what you're not used to is challenging!

Just one point:

I have to accept that counting the ranges in a range with no entries does not produce "0". but I don't like it

In the "value not found" situation, we're not dealing with a range which contains no entries - we're dealing with a range that hasn't been defined.

Best regards,

Greg M

[Ramses505]

Thanks Greg, to be clear, I was (kind of) joking.

Have a good one.

[Greg M]

Hi again,

Ok on that.

Many thanks for the Reputation increase - much appreciated!

Best regards,

Greg M