I understand ithat it is not possible to use an array formula in VBA using
the " .formula = " context. Can someone show me the steps, and/or the logic
for creating an array formula in VBA? I don't want these 30 array formulas
to stay in the worksheet, since I copy this sheet 60-120 times within a
workbook. Although (if possible) I could copy the formula in, paste the
values, and then delete the formulas, I would rather just pass the values to
the proper cells. The formula uses two criteria to find a value in a table
on a separate sheet within the workbook:

{=IF(ISERROR(INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAIL,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAIL,0)))}

"RETAIL" is a range (one column) containing retail prices
"CONCAT" is another one column range in the same table which concatenates a
product code

The sheet that uses this formula (on 32 lines) is replicated 60-120 times
within the workbook, which slows everything down and creates bloat. I've
converted the rest of my formulas to VBA, but this one is hard for me to
understand. Any ideas would be greatly appreciated!

YourRange.FormulaArray =

PV Jefe wrote:
> I understand ithat it is not possible to use an array formula in VBA using
> the " .formula = " context. Can someone show me the steps, and/or the logic
> for creating an array formula in VBA? I don't want these 30 array formulas
> to stay in the worksheet, since I copy this sheet 60-120 times within a
> workbook. Although (if possible) I could copy the formula in, paste the
> values, and then delete the formulas, I would rather just pass the values to
> the proper cells. The formula uses two criteria to find a value in a table
> on a separate sheet within the workbook:
>
> {=IF(ISERROR(INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAIL,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAIL,0)))}
>
> "RETAIL" is a range (one column) containing retail prices
> "CONCAT" is another one column range in the same table which concatenates a
> product code
>
> The sheet that uses this formula (on 32 lines) is replicated 60-120 times
> within the workbook, which slows everything down and creates bloat. I've
> converted the rest of my formulas to VBA, but this one is hard for me to
> understand. Any ideas would be greatly appreciated!

Muchas Gracias!! That was toooo easy! I think I tend to overcomplicate
things!!

"(E-Mail Removed)" wrote:

>
> YourRange.FormulaArray =
>