Array Formula Index Match formulas in VBA problem
 

I have an array formula in an excel worksheet which compares values o
one sheet to values on another and returns matching data the record.

This formula is as follows:

=INDEX(Subjects!$E$31:$E$511, MATCH($A14 & $M14 & $J14,
Subjects!$B$31:$B$511 & Subjects!$K$31:$K$511 & Subjects!$I$31:$I513
0))

As you can see it is matching 3 columns on the current sheet to
columns on the subjects sheet and returning the value in column E o
the subject sheet for the matching record. This all works fine.

But I would like to move the formula out of the spreadsheet and int
some VBA code, so it will work on the press of a button on th
spreadsheet instead.

The problem is, I can get this type of formula to work in VBA whe
there is a single match e.g.

ActiveCell.Offset(0, 4)
Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("B31:N513")
Application.WorksheetFunction.Match(Comsubno.Value
Worksheets("Subjects").Range("B31:B513"), 1), 13)

But I cannot get multiple matches to work e.g.

ActiveCell.Offset(0, 11)
Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("D31:D511")
Application.WorksheetFunction.Match(Comsubno.Value & Comlabproc.Value
Comtimept.Value, Worksheets("Subjects").Range("B31:B511")
Worksheets("Subjects").Range("K31:K511")
Worksheets("Subjects").Range("I31:I511"), 0))

Any Ideas anyone ????

--
Message posted from http://www.ExcelForum.com

Array Formula Index Match formulas in VBA problem
 

Yes. VBA doesn't support Array evaluation of formulas.

You would have to construct a string that would represent the formula as you
would enter it in a cell, and then use the Evaluate function.

--
Regards,
Tom Ogilvy

"hawkit " wrote in message
...
I have an array formula in an excel worksheet which compares values on
one sheet to values on another and returns matching data the record.

This formula is as follows:

=INDEX(Subjects!$E$31:$E$511, MATCH($A14 & $M14 & $J14,
Subjects!$B$31:$B$511 & Subjects!$K$31:$K$511 & Subjects!$I$31:$I513,
0))

As you can see it is matching 3 columns on the current sheet to 3
columns on the subjects sheet and returning the value in column E on
the subject sheet for the matching record. This all works fine.

But I would like to move the formula out of the spreadsheet and into
some VBA code, so it will work on the press of a button on the
spreadsheet instead.

The problem is, I can get this type of formula to work in VBA when
there is a single match e.g.

ActiveCell.Offset(0, 4) =

Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("B31:N513")
,
Application.WorksheetFunction.Match(Comsubno.Value ,
Worksheets("Subjects").Range("B31:B513"), 1), 13)

But I cannot get multiple matches to work e.g.

ActiveCell.Offset(0, 11) =

Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("D31:D511")
,
Application.WorksheetFunction.Match(Comsubno.Value & Comlabproc.Value &
Comtimept.Value, Worksheets("Subjects").Range("B31:B511") &
Worksheets("Subjects").Range("K31:K511") &
Worksheets("Subjects").Range("I31:I511"), 0))

Any Ideas anyone ?????


---
Message posted from http://www.ExcelForum.com/