Using For..Each and Find to search for an item

[staggers47]

Hi,

I have a named range of cells to search. It's called StdEng, and is about 60K cells

I'm none too clear about the syntax of the For Each loop, or Find.

What I need to do is search through looking for a match for 'SearchItem'

1. It needs to search by columns (because a match is most likely to be in the first column).

2. The match must be whole word match only (case insensitive).

3. If a match is found, I need to know what cell it was found in, to pull out info from other cells in the same row.

If there is a match, there will only be one, so it can stop searching if one is found.

Also, it's likely that around half the cells could be empty, in case that makes a difference.

Any help much appreciated.

Once I see a version that works, then maybe I'll finally get my head around it!

[Ron Coderre]

Perhaps something like this...
C1: a term to match as a whole word....dogsled
(So "my dogsled is fast" would be a match, but not "dogsledding is fun")

This regular formula returns the contents of the first cell in StdEng that contains the C1 word

Please Login or Register  to view this content.

Is that something you can work with?

[staggers47]

Hi Ron,

I altered it slightly so that the search term was in F1 instead of C1, and put the formula in E1 instead of D1, but the answer I got was 'N/A' in the cell, with the comment that 'a value was not available', with no further explanation, so I'm none the wiser!

But I think I'd rather do it in code than have it as a cell formula anyway, because there's going to be a lot of this searching going on!

[Ron Coderre]

If there was a matching item, the formula should have found it.
You'll need to post a sample of the kind of data that will be encountered so we tailor a solution for you.

[staggers47]

Hi again,

Here is how I modified your code, so the search word goes in F1, and I put the formula in E1.


E1: =INDEX(StdEng,MATCH("* "&F1&" *",INDEX(" "&StdEng&" ",0),0))

Here is a snippet form the first col of the StdEng named range. As you can see, it's about things that rhyme (and why it has to be a whole word match).




brash
dash
flash
gash
hash
cash
clash
crash
lash
mash
gnash
plash
rash
sash
slash
smash
splash
stash
trash
thrash

When I entered the formula, it did turn the StdEng terms blue, so it knew it was there, but whatever I enter in F1 I just get 'a value was not available to the formula'.

Am I doing something stupid (probably)?

[Ron Coderre]

I got the formula and your example to work just fine.

Is that error thrown by a macro?
If yes, can you post the offending code?

[staggers47]

Working now. Copied it, closed the book, put it back, now it works. Obviously something running somewhere it didn't like!

Thanks Ron.