excel runtime error 13; type miss match

Having a little trouble using this formula and others in vba. Can anyone help?

thanks
tds

Please Login or Register  to view this content.

What you trying to do?

My guess is that there are 765,000 keywords listed in column E and the goal is to find the first keyword that is in A5.

If there are no keywords in A5, the vba will give a mismatch error.

But not only that...

LOOKUP search for 32768 while there is 765000 entries...

terrysoper1973, can you upload example workbook?

The array being searched is an array of positions in A5. Most of the array would be #VALUE errors, when the key word isn't present. That would be more of a problem than the 2^15.

I would use an underlying formula of
=INDEX(E1:E1000, MATCH(LEN(A5),SEARCH(E1:E1000,A5&E1:E1000),-1)+1, 1)

But since its a CSE formula, one would have to use Evaluate

Please Login or Register  to view this content.

I tried the index match method, but got run time errors with vba(evaluate), and #value with =index(formula

this formula does work in the spreadsheet but not in vba. I'm using it as a city look up list to extract the city from a raw data cell. I've set a5 to a value I know is in the lookup
=LOOKUP(2^15,SEARCH(sheet2!$E$1:$E$765344,A5),sheet2!$E$1:$E$765344)

I've set a5 to a value I know is in the lookup list to test your formula below, but just got back a #value error. and excel didn't seem to be searching the lookup list; it was too fast.
=INDEX(E1:E1000, MATCH(LEN(A5),SEARCH(E1:E1000,A5&E1:E1000),-1)+1, 1)

Have you tried

Please Login or Register  to view this content.

BTW, with 765,000 key words being searched, I have to ask "how many words are there in the english language?"

171,476 words in english, but city names are what im looking up and they can be in any language. I have an exhaustive city list that also contains spelling variations like st tomas, st. tomas, saint tomas. Thats where the 765,000 comes from.

I have a test file that is simpler that I'll try your code on.

Thanks for your help

If you could attach a portion of your test file, that would help. (A representative portion, the web-site would explode if you tried to attach your whole file.)
The sheer bulk of your dataset is one of the issues.
I presume that since you have "st thomas", "st. thomas" and "saint thomas", you also have "st louis", "st. louis" and "saint louis"

One approach would be to a list equivalent prefixes like "st", "st.", "saint" while the master list only has "st thomas" and "st louis". That has shortened even this trivial list from 6 cells to 5.

I set up a test file. a lookup table from a1:a3 with values a1=d a2=dd a3=ddd and whats in b1 is the value i want to look up. I set b1=d to see what the code below would return. I want it to find the first match which should be
a1=d
Instead it finds "dd" which is in the lookup table range a2. does this help a little.

Please Login or Register  to view this content.

test vba.xlsm

ok here is a small test file that should clear things up. My country is small and only has three citys

ok got it work thanks to you telling me what happens to lookup functions when searching a range, errors in array kind of like. here is the code.

Please Login or Register  to view this content.

a lil better error handling

Please Login or Register  to view this content.

shg MVP

Please Login or Register  to view this content.