Converting an array formula to VBA

P

PV Jefe

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Array Help in VBA 3
Creating an ARRAY UDF 0
Look for an efficient way to handle array formula with VBA 3
Cell values to an array, then array transpose to another workbook 8
Return BLANK is cell value sought is blank 13
Indirect/match formula - array 1
shortening code by defining variables using an array? 2
need help in fine-tuning a formula that removes blank cells in a column range 3

Top