Find Text VBA code

**MARKSTRO** · 08-31-2011, 02:40 PM

The following VBA code allows me to type in cell A6 what I want to find in the sheet. Right now it searches the whole sheet, how can I narrow the search to a range and match exactly what is in A6. I have tried editing line 2 with a range and line 4 in a couple ways and can't get it.

Please Login or Register  to view this content.

I have tried the following to narrow the range and find exact match

first attempt on line 3 of original code

Please Login or Register  to view this content.

second attempt on line 3 of original code

Please Login or Register  to view this content.

After these attempts and returning the code to the original, the whole process breaks down and won't search anywhere in the sheet, The return just goes to A11.

Any suggestions, this sheet searches for the location of a truck among 767 entries, I would also like to expand to VIN and license # searches, hence the exact match attempt.
please see attached file.

**royUK** · 08-31-2011, 02:44 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**MaczaQ** · 08-31-2011, 03:22 PM

Hello,

I think that it is because .Find method gives you only first Match after Target.
You should use some loop and method .FindNext. (Collect match addresses in colleciton or in range union)
I will think about some example for you.

Best Regards
MaczaQ

**MaczaQ** · 08-31-2011, 03:58 PM

So I discover a way

please try following code

Please Login or Register  to view this content.

Best Regards
MaczaQ

**MARKSTRO** · 08-31-2011, 04:40 PM

Still no joy, when I press return after entering the search text the return goes to cell A12.
How do I attach the latest sheet in a reply for you to look at?

**MaczaQ** · 08-31-2011, 04:50 PM

use "Go Advanced" or "New Post" as a replay then attach document

**MARKSTRO** · 08-31-2011, 05:08 PM

Thanks, see attached.
It seems the code will work a couple of times then stop working, not sure if I have a setting set wrong or lose something when I save/quit and then reopen the file.
Hope you can make this work for me, I thought I would have it solved by now on my own.
thanks,

**MaczaQ** · 09-01-2011, 06:52 AM

better solution then previous one, just init it with search value in your code (example. LookingFor "abcd")

Please Login or Register  to view this content.

Best Regards

**MARKSTRO** · 09-01-2011, 11:22 AM

Do I insert A7 as my search box for the A1 references in line 2?

**MaczaQ** · 09-01-2011, 01:27 PM

You can do this for select "A7" when nothing is found
But previously you should refer to this procedure when Target (A7) is changed

for example:

Please Login or Register  to view this content.

Best Regards
MaczaQ

Find Text VBA code

LinkBack

Thread Tools

Rate This Thread

Display

Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Re: Find Text VBA code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions