I am trying to find the string position of a string, and the string I am looking FOR is from an array of terms in another worksheet. I have it looking like this, but am getting a "type mismatch error" on this line...
Please Login or Register to view this content.
Here is my entire code. Any help will be appreciated.
Please Login or Register to view this content.
InStr locates a substring within another string, not in an array.
You might use the Match function.
Entia non sunt multiplicanda sine necessitate
Thanks, but match function won't work for my needs. I need to have the position so that I can use the mid function to make modifications.
So to use inStr, I will need to loop through the array. Right?
That's what the match function does. Read shg's post again.
it looks like MATCH does just the opposite. It looks for a substring within an array. My array contains the substrings, and I need to find substring position within the string. Is there a way to do this?
I don't know where you're looking. Why don't you attach a sample of what you're doing?
So you are, sorry.
You can do this with an array formula from the user interface, e.g.,
{=MATCH(TRUE, ISNUMBER(SEARCH(B1:B3, A1)), 0)}
... but in VBA, I think you need to iterate through the array.
Last edited by shg; 09-30-2008 at 12:16 PM.
Hello friscokid,
This version of your macro takes advantage of the Range Objects properties and methods to eliminate the need for nested looping and a dynamic array. This code can be modified to find multiple occurances of the substring quite easily, if you need that feature later.
Sincerely,Please Login or Register to view this content.
Leith Ross
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks