0

My ultimate goal is to shorten this formula using a user definded function in VBA, with the following as my parameter A1:C10, A1:A10, E1, B1:B10, F1 (this will vary, this is just an pure example)

Excel formula: 

=index(A1:C10,match(1,(A1:A10=E1)*(B1:B10=F1),0),G1)

Using user defined function, it gonna be something like this: 

=pick(A1:C10, A1:A10, E1, B1:B10, F1, G1)

My VBA code is like this, however, it does not work. Please expert, help me. Many thanks.

Function pick(range As range, con1 As range, con1a As range, con2 As range, con2a As range, col As range) As Variant

pick = WorksheetFunction.Index(range, Match(1, (con1 = con1a) * (con2 = con2a), 0), col)

End Function
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Kira
  • 1
  • VBA can't do matrix operations such as `con = con1a`. You will need to do that in a loop. – YowE3K Dec 24 '17 at 04:32
  • Hi, would you mind sharing the code of how you would do this kind of stuff ? Sorry, I am a noob and hope that I can learn from this little exercisee. Thanks – Kira Dec 24 '17 at 07:12
  • showing an example with expected output would also help. – QHarr Dec 24 '17 at 08:35

1 Answers1

0

use the Evaluate method:

Function pick(range As range, con1 As range, con1a As range, con2 As range, con2a As range, col As range) As Variant

pick = Application.Caller.Parent.Evaluate("Index(" & range.Address(1,1,,1) & ",Match(1, (" & con1.Address(1,1,,1) & " = " & con1a.Address(1,1,,1) & ") * (" & con2.Address(1,1,,1) & " = " & con2a.Address(1,1,,1) & "), 0), " & col.Address(1,1,,1) & ")")

End Function
Scott Craner
  • 148,073
  • 10
  • 49
  • 81