Converting an array formula to VBA
 

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&RETAI L,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETA IL,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!

Converting an array formula to VBA
 

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&RETAI L,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETA IL,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!

Converting an array formula to VBA
 

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

" wrote:


YourRange.FormulaArray =