1

I need to create a procedure to return the result of a multi search criteria. I found the post below which is an excellent solution, but some details are missing. How can I pass the variables that define the search to the evaluate statement ?

VBA (Excel): Find Based on Multiple Search Criteria Without Looping From the Post:

Sub GetEm2()
x = Filter(Application.Transpose(Application.Evaluate("=IF((LEFT(A1:A10000,4)=""fred"")*(B1:B10000>date(2001,1,1))*(C1:C10000=""apple""),ROW(A1:A10000),""x"")")), "x", False)
End Sub

How do I solve this problem ? the code below doesn't work:

Sub GetEm2(ByRef myRow As Long, ByVal serch1 As String, ByVal Search2 as String, ByVal search3 as String)
  x = Filter(Application.Transpose(Application.Evaluate("=IF((LEFT(A1:A10000,4)=search1)*(B1:B10000=search2)*(C1:C10000=search3),ROW(A1:A10000),""x"")")), "x", False)   
  myRow = Clng(x)
End Sub

Thanks a lot by your answers, JD

user19655837
  • 11
  • 2

1 Answers1

2

Please, try the nex way:

Sub GetEm2(ByRef myRow As Long, ByVal search1 As String, ByVal search2 as String, ByVal search3 as String)
  myRow  = Filter(Application.Transpose(Application.Evaluate("=IF((LEFT(A1:A10000,4)=" & _
                       search1 & ")*(B1:B10000=" & search2 & ")*(C1:C10000=" & search3 & _
                                                  "),ROW(A1:A10000),""x"")")), "x", False)   
End Sub

Your code did not use variables if you use their name as strings inside of a bigger string...

T.M.
  • 9,436
  • 3
  • 33
  • 57
FaneDuru
  • 38,298
  • 4
  • 19
  • 27