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

Search Excel VBA help for: FormulaArray

Per that topic, the formula in the VBE needs to be in R1C1 format

Example:
Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

wrote in message
...
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

VBA's help is in error. You don't have to use R1C1 reference style.

Ron Coderre wrote:

Search Excel VBA help for: FormulaArray

Per that topic, the formula in the VBE needs to be in R1C1 format

Example:
Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

wrote in message
...
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

What's the world coming to when you can't trust MS Help? : \

Thanks, Dave

"Dave Peterson" wrote in message
...
VBA's help is in error. You don't have to use R1C1 reference style.

Ron Coderre wrote:

Search Excel VBA help for: FormulaArray

Per that topic, the formula in the VBE needs to be in R1C1 format

Example:
Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

wrote in message
...
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

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

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

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

Thank you very much dave,

I have another question, please help me dave

I have folder contains 10 to 15 excel file.

I want a macro
First step - if i run that macro it ask the user to select the file.
Second step - If user choose 3 files
Third step - All the 3 files data ( data are always on sheet2) to be
copied.
Fourth step - copied data to be pasted in another file.

I am looking for your help dave.


then sheet1 of selected files data to copied and pasted in another
workbook.


On Mar 25, 7:37*pm, Dave Peterson wrote:
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

You can use application.getopenfilename and multiselect:=true to allow the user
to select more than one file.

But there are lots of ways to "combine" data from different
workbooks/worksheets.

Ron de Bruin has tons of examples he
http://www.rondebruin.nl/tips.htm
look for Copy/Paste/Merge examples

But here's some code that may get you started:

Option Explicit
Sub testme01()

Dim myFileNames As Variant
Dim RptWkbk As Workbook
Dim wkbk As Workbook
Dim fCtr As Long

myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

Set RptWkbk = Workbooks.Add(1)
RptWkbk.Worksheets(1).Name = "DeleteMeLater"

For fCtr = LBound(myFileNames) To UBound(myFileNames)
'open each workbook that the user selected
Set wkbk = Workbooks.Open(Filename:=myFileNames(fCtr), ReadOnly:=True)

'this is the part that would do the combining
wkbk.Worksheets("Sheet2").Copy _
after:=RptWkbk.Worksheets(RptWkbk.Worksheets.Count )

'close that workbook
wkbk.Close savechanges:=False
Next fCtr

Application.DisplayAlerts = False
RptWkbk.Worksheets("Deletemelater").Delete
Application.DisplayAlerts = True

End Sub

There is no validation checking that the sheets are named correctly.

wrote:

Thank you very much dave,

I have another question, please help me dave

I have folder contains 10 to 15 excel file.

I want a macro
First step - if i run that macro it ask the user to select the file.
Second step - If user choose 3 files
Third step - All the 3 files data ( data are always on sheet2) to be
copied.
Fourth step - copied data to be pasted in another file.

I am looking for your help dave.

then sheet1 of selected files data to copied and pasted in another
workbook.

On Mar 25, 7:37 pm, Dave Peterson wrote:
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

--

Dave Peterson

Thanks a lot Dave.


On Mar 26, 3:47*am, Dave Peterson wrote:
You can use application.getopenfilename and multiselect:=true to allow the user
to select more than one file.

But there are lots of ways to "combine" data from different
workbooks/worksheets.

Ron de Bruin has tons of examples hehttp://www.rondebruin.nl/tips.htm
look for Copy/Paste/Merge examples

But here's some code that may get you started:

Option Explicit
Sub testme01()

* * Dim myFileNames As Variant
* * Dim RptWkbk As Workbook
* * Dim wkbk As Workbook
* * Dim fCtr As Long

* * myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
* * * * * * * * * * * MultiSelect:=True)

* * If IsArray(myFileNames) = False Then
* * * * Exit Sub
* * End If

* * Set RptWkbk = Workbooks.Add(1)
* * RptWkbk.Worksheets(1).Name = "DeleteMeLater"

* * For fCtr = LBound(myFileNames) To UBound(myFileNames)
* * * * 'open each workbook that the user selected
* * * * Set wkbk = Workbooks.Open(Filename:=myFileNames(fCtr), ReadOnly:=True)

* * * * 'this is the part that would do the combining
* * * * wkbk.Worksheets("Sheet2").Copy _
* * * * * * after:=RptWkbk.Worksheets(RptWkbk.Worksheets.Count )

* * * * 'close that workbook * * * *
* * * * wkbk.Close savechanges:=False
* * Next fCtr

* * Application.DisplayAlerts = False
* * RptWkbk.Worksheets("Deletemelater").Delete
* * Application.DisplayAlerts = True

End Sub

There is no validation checking that the sheets are named correctly.





wrote:

Thank you very much dave,

I have another question, please help me dave

I have folder contains 10 to 15 excel file.

I want a macro
First step - if i run that macro it ask the user to select the file.
Second step - If user choose 3 files
Third step - All the 3 files data ( data are always on sheet2) to be
copied.
Fourth step - copied data to be pasted in another file.

I am looking for your help dave.

then sheet1 of selected files data to copied and pasted in another
workbook.

On Mar 25, 7:37 pm, Dave Peterson wrote:
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

--

Dave Peterson- Hide quoted text -

- Show quoted text -