Search text in an array element

[mchac]

I have some messy VBA that will search each element of an array for particular words. The array elements may have (likely do have) more text than just that one word that I'm searching for. I've used InStr to cobble together a solution bit it's very clunky and slow. (Rick if you are reading this post, I've not had a chance to read your ExcelFox InStr post yet).

Rather than use my crude loop I'd like to use .find. However the VBA help uses the following syntax:
object.Find(target, startline, startcol, endline, endcol [, wholeword] [, matchcase] [, patternsearch]) As Boolean

But the help info I find on the web uses:
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Does anyone know the difference between these two i.e. in what cases are they each used and can I use the first one to look through an array for a string?

Thanks

 

[JLGWhiz]

The first one is for searching in a code module and the second one is for doing a Range.Find on a worksheet.

 

[mchac]

ok, thanks jlg.
do you know if either will work to search elements of an array for specific text?

 

[Rick Rothstein]

ok, thanks jlg.
do you know if either will work to search elements of an array for specific text?

When you say "array", you mean a VBA String array, correct? If so, you can use the Filter function and test the UBound for the array it returns, but note that the Filter function has the same "flaw" that InStr has... you cannot stop it from finding the word embedded within other text. I think it may be time for you to read my InStrExact mini-blog article here...

InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word

 

[mchac]

Thanks Rick.
I've read your article but need to test it to understand exactly what's going on.

My post above wasn't clear enough. The background is that I understand that reading sheet contents into an array, performing work on the array then writing the results to a sheet is faster than working interactively with a sheet to get to the same place. That is from a long time ago so may no longer be true.

With that in mind I've taken the contents of a 6000 x 2 range of a sheet (all text) and written it to an array.
Then I'm testing the first array column contents to see if a word is present in any of the elements. If it is present, I move all the remaining array element up one position, so I overwrite the elements I don't want. Then at the end of the array I write in a "" so I don't have duplicates of the last element.

One of my tests uses this code nested in a For Next that increments the ARRAYROWCOUNTER variable. As you can see if the string appears then I call another sub that moves the elements up one position.

If InStr(1, NAME_TKR_ARRAY(ARRAYROWCOUNTER, 1), "fund ", 1) <> 0 Then
MoveArrayElementsUp NAME_TKR_ARRAY, ARRAYROWCOUNTER, NUMROWS3

As you can see, I'm testing for the word "fund " but I recoginize that I'm open to a word like XXXfund slipping through. Subject to finding a better solution, I think I would adjust the code to test for " fund " OR " fund" where the 'd' is the last character in the array element to fix this.

You asked if I mean a VBA string array. Sorry but I'm not sure what that means. I my 6000 x 2 array the elements are all strings.

Also, I understand that Filter only works on 1 dimensional arrays so I can't use it. I had hoped that .find would work to leverage Excels precooked capabilities but I can't find a way to make that work on the array.

So I'm trying to find a faster solution to test for the presence of particular words in this array. The time saving may be small on this task, however the next task I have is to do something similar on two different 300,000 x 4 arrays so a small time saving could be meaningful. This is also in the context of having quite limited and rusty VBA knowledge.

Any thoughs are appreciated.