First, I wouldn't take the chance of concatenating those cells and finding a
match when there really isn't one.

aaa bbb ccc
and
aa ab bbccc

would both return a match--even if the corresponding cells in the table
contained:
a a abbbccc

I'd use an array formula like:
=match(1,(b2=sheet2!a1:a10)*(d2=sheet2!b1:b10)*(f2 =sheet2!c1:c10),0)
(still array entered)

And if you're not using xl2007, then you can't use the entire column in array
formulas.

In code, I'd use:

Dim myFormula As String
Dim res As Variant

myFormula _
= "match(1,(b2=sheet2!a1:a10)*(d2=sheet2!b1:b10)*(f2 =sheet2!c1:c10),0)"

res = Worksheets("Sheet1").Evaluate(myFormula)

If IsError(res) Then
MsgBox "No match!"
Else
MsgBox res
End If


Since the code used worksheets("Sheet1").evaluate(), those unqualified B2, D2
and F2 will belong to Sheet1.




wrote:

Hi,

I have formula ( Array formula)
=MATCH(B2&D2&F2,Sheet2!A:A&Sheet2!B:B&Sheet2!C:C,0 )

How Can we use in VBA
Application.worksheetfunction.match..........

How should I use same array formula in VBA.

Thanks in advance

--

Dave Peterson