First, What happened to the worksheets(...).evaluate() statement? I didn't use
application.worksheetfunction.match().
Second, I would remove the evaluating from the code and just plop the formula
into the worksheet cell. If you wanted values, you can copy|paste
special|Values (in code) later.
Option Explicit
Sub testme()
Dim LastRow As Long
Dim myFormula As String
Dim ShN As String
ShN = "sheet2"
'=match(1,(b2=sheet2!a:a)*(d2=sheet2!b:b)*(f2=shee t2!c:c),0)
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
myFormula = "match(1,(b2='" & ShN & "'!a:a)" _
& "*(d2='" & ShN & "'!b:b)" _
& "*(f2='" & ShN & "'!c:c),0)"
myFormula = "=if(isnumber(" & myFormula & "),""repeated"","""")"
.Cells(2, "I").FormulaArray = myFormula
With .Cells(2, "I").Resize(LastRow - 2 + 1, 1)
.FillDown
.Value = .Value
End With
End With
End Sub
wrote:
Hi Dave,
Thanks for your reply
I am using 2007
I am getting result as "repeated" even if it is not matching
here is my code
r3 = Cells(Rows.Count, "C").End(xlUp).Row
For a = 2 To r3
'where shn = sheet2
' sheet name add evertime when i run the code, so doesn't remain
constant
a1 = Cells(a, 2)
a2 = Cells(a, 4)
a3 = Cells(a, 6)
a4 = Cells(a, 7)
b1 = Worksheets(shn).Range("A:A")
b2 = Worksheets(shn).Range("B:B")
b3 = Worksheets(shn).Range("C:C")
b4 = Worksheets(shn).Range("D:D")
res = Application.WorksheetFunction.Match(1, (a1 = b1) * (a2 = b2) *
(a3 = b3) * (a4 = b4), 0)
If Not IsError(res) Then Cells(a, "i") = "repeated"
Next a
On Mar 25, 5:47 pm, Dave Peterson wrote:
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
--
Dave Peterson