Search Across Range works in Excel but not VBA

[Marmit424]

Hey,

Quick background. I was using Match to search for a string across a range. I ran into the 255 character limit on the match function. I came across a post that showed how you could use Search as in the function below to overcome this limit -

=MATCH(TRUE,INDEX(ISNUMBER(SEARCH(H2,R1:R5)),0),0)

This works wonderfully in Excel. I then replicated this function in VBA and it returns error mismatch on the Search statement.

LookupWorksheet = "Sheet 2"
LookupValue = "*text here"
LookupRange = "$B$1:$B$5"


Example = WorksheetFunction.Match(True, WorksheetFunction.Index(WorksheetFunction.IsNumber(WorksheetFunction.Search(LookupValue, Worksheets(LookupWorksheet).Range(LookupRange))), 0), 0)

I've found complicated macros that change a simple search function into multi-step For Each Cell in Range statements. Is there a particular reason why Excel can complete a Search across multiple cells while VBA can't? I believe I'm making a simple syntax error or misunderstanding a logical limitation of VBA, perhaps Excel is returning an array through the search and VBA cannot return an array on a search function because it handles arrays differently.

Thanks!

 

[Domenic]

You can use the Evaluate method...

Example = Evaluate("MATCH(TRUE,INDEX(ISNUMBER(SEARCH(""" & LookupValue & """," & LookupRange & ")),0),0)")

Note that the length of the Name used in the Evaluate method is limited to 255 characters. Also, since you're using the SEARCH function, the following would suffice...

LookupValue = "text here"

The SEARCH function will match the text anywhere with the the string, whether at the beginning, in the middle, or at the end.

Hope this helps!

 

[Marmit424]

You are my hero