FindAll function

[snb]

Hi Rock,

Intriguing code.

I tried to rewrite the function and to do the same thing in a macro.
I'm not sure whether you prefer all separate address entries as result or entries the way Excel describes ranges/areas, using :

In the macro I introduced the use of Evaluate (in the [ ] form).

Sub tst1()
  MsgBox findall_snb1("cut", "A1:E5", True, True)
  MsgBox findall_snb1("cut", "A1:E5", True, False)
  MsgBox findall_snb1("cut", "A1:E5", False, True)
  MsgBox findall_snb1("cut", "A1:E5", False, False)
End Sub

Function findall_snb1(c01, c02, Optional opt_1 As Boolean, Optional opt_2 As Boolean)
  ' c01 search string
  ' c02 range to be searched in
  ' opt_1 whole match=true, partial match=false
  ' opt_2 case sensitive=true, case insensitive=false

  For Each cl In Range(c02)
    If opt_1 And opt_2 And StrComp(cl, c01, vbTextCompare) = 0 Then findall_snb1 = findall_snb1 & "_" & cl.Address
    If opt_1 And opt_2 = False And cl = c01 Then findall_snb1 = findall_snb1 & "_" & cl.Address
    If opt_1 = False And opt_2 And InStr(cl, c01) Then findall_snb1 = findall_snb1 & "_" & cl.Address
    If opt_1 = False And opt_2 = False And InStr(1, cl, c01, vbTextCompare) Then findall_snb1 = findall_snb1 & "_" & cl.Address
  Next
End Function

Sub tst2()
  findall_snb2 "cut", "A1:E5", True, True
  findall_snb2 "cut", "A1:E5", True, False
  findall_snb2 "cut", "A1:E5", False, True
  findall_snb2 "cut", "A1:E5", False, False
End Sub

Sub findall_snb2(c01, c02, opt_1, opt_2)
  ' c01 search string
  ' c02 range to be searched in
  ' opt_1 whole match=true, partial match=false
  ' opt_2 case sensitive=true, case insensitive=false

  Names.Add "snb_1", "=" & Chr(34) & c01 & Chr(34)
  Range(c02).Name = "snb_2"
  Names.Add "snb_3", "=" & 1 - opt_1 - 2 * opt_2

  sn = [if(choose(snb_3,iserror(search(snb_1,snb_2)),not(snb_1=snb_2),iserror(find(snb_1,snb_2)),not(exact(snb_1,snb_2))),"",address(row(snb_2),column(snb_2)))]

  For Each cl In sn
    If cl <> "" Then
      If IsEmpty(c03) Then
         Set c03 = Application.Union(Range(cl), Range(cl))
      Else
         Set c03 = Application.Union(c03, Range(cl))
      End If
    End If
  Next

  Debug.Print [snb_3] & "__" & c03.Address
End Sub