Array Formula Index Match formulas in VBA problem

[hawkit]

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("Subjects").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("Subjects").Range("D31511")
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 ????

 

[Tom Ogilvy]

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.