Using Find to test if element exist in an Range Array

[Erick]

Hi all, I am looking to use the 'Find' vba function to test if an element exist in a range array but I am getting a 'Type mismatch error'.

When I swap my array from a range to a list, it works.

Please help.

Sub FindBob()
   'Create Array
   Dim a As String
   Dim strName As Variant
   
   strName = Range("A1:A5") 'is generates a Type mismatch error - the range contains the same elements as the list below
   
   'strName = Array("Bob Smith", "John Davies", "Fred Jones", "Steve Jenkins", "Bob Williams") - this method works
   
   'declare a variant to store the filter data in
   Dim strSubNames  As Variant

  'filter the original array
 
   strSubNames = Filter(strName, "Bob")

   'if you UBound value is greater than -1, then the value has been found
   If UBound(strSubNames, 1) > -1 Then MsgBox ("I found Bob")
End Sub

 

[Joe4]

You should be able to use "Match", as shown here: Check if a value is in an array or not with Excel VBA.

 

[Peter_SSs]

Change

strName = Range("A1:A5")

to

strName = Application.Transpose(Range("A1:A5"))

Also be aware that if Bob Smith and Bob Williams did not appear in the list but Bobette Jones did, the message box would still say "I found Bob"

 

[Erick]

Thanks Peter and Joe. I have a large data, which of the 2 options (match or filter) is more efficient?
Also, I tried the match option as you suggested, with the line of code below but I am getting an Error 2042, which I read was effectively an N/A result.

"If Not IsError(Application.Match("Bob", strName, 0)) Then MsgBox ("I found Bob again")"

 

[Joe4]

Thanks Peter and Joe. I have a large data, which of the 2 options (match or filter) is more efficient?
Also, I tried the match option as you suggested, with the line of code below but I am getting an Error 2042, which I read was effectively an N/A result.

"If Not IsError(Application.Match("Bob", strName, 0)) Then MsgBox ("I found Bob again")"

That is not quite the way they show in the code. They show a format of:

If IsNumeric(Application.Match("Bob", strName, 0)) Then MsgBox ("I found Bob again")"

and that is assuming that strName is an array, and not a range.

 

[Peter_SSs]

Can you confirm whether or not you want to show "Found" if the text being searched for is a sub-string of a longer text as in the example at the bottom of my previous post?