0

Is there any way to do that in VBA other than manually looping through the array and testing each element separately?

Community
  • 1
  • 1
LucasSeveryn
  • 5,984
  • 8
  • 38
  • 65
  • Are you asking is there a way of testing if any sub-string, within array of sub-strings, exists within a string with running a loop? If so, I don't believe there is – Jeremy Nov 21 '16 at 10:25
  • @LucasSeveryn you can use a `For` loop, and check each member of the list of strings (using `Instr` function for instance). Another option is using the `Match` function. But you need to provide more data, or upload your latest code attempt – Shai Rado Nov 21 '16 at 11:11

1 Answers1

1

Check out A.S.H's answer below, Application.Match is the way to go. This will throw Error 2042 if the element is not in the array.

Answer mentioned: Checking if Value is Present in an Array

MSDN: https://msdn.microsoft.com/en-us/library/bb239415(v=office.12).aspx

Community
  • 1
  • 1
Preston
  • 7,399
  • 8
  • 54
  • 84
  • When I tried this method `Application.Match` returned error `13 - Type Mismatch` while `Worksheetfunction.Match` returned error `1004 - Unable to get the Match property of the WorksheetFunction class`. Error 1004 would be more specific in an error handler, while `Application.Match` could return the boolean exists/not exists value with `bExists = Not (IsError(Application.Match("MyValueString", vMyArray, 0)))` (rather than put it in an `IF...END IF` block to check for an error). – Darren Bartrup-Cook Nov 21 '16 at 11:52