Help converting formula to VBA code

[Sthrncali]

I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.

Here is the formula as it's entered in my spreadsheet:

=INDEX(PerfMetricTbl!$U$2:$U$250000, MATCH(1, (PerfMetricTbl!$A$2:$A$250000=$A2)*(PerfMetricTbl!$D$2:$D$250000=$D2)*(PerfMetricTbl!$Q$2:$Q$250000=$Q2)*(PerfMetricTbl!$R$2:$R$250000=Z$1), 0)))


I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...

The end result should be:

For each cell in myRange
cell.value = 'Formula result here
Next cell

-OR-

If possible to fill the range in one step:

myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)

If someone can give me a more efficient solution I am all ears. The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.

Thanks in advance for your help!

 

[AlphaFrog]

Here's a start...

Sub test()
    
    Dim MyRange As Range
    
    ActiveWorkbook.Names.Add Name:="pmtA", RefersTo:="=PerfMetricTbl!$A$2:$A$250000"
    ActiveWorkbook.Names.Add Name:="pmtD", RefersTo:="=PerfMetricTbl!$D$2:$D$250000"
    ActiveWorkbook.Names.Add Name:="pmtR", RefersTo:="=PerfMetricTbl!$R$2:$R$250000"
    ActiveWorkbook.Names.Add Name:="pmtQ", RefersTo:="=PerfMetricTbl!$Q$2:$Q$250000"
    ActiveWorkbook.Names.Add Name:="pmtU", RefersTo:="=PerfMetricTbl!$U$2:$U$250000"
    
    Set MyRange = Sheets("Sheet1").Range("AA2:AA10000")
    
    MyRange.FormulaArray = "=INDEX(pmtU,MATCH(1,(pmtA=RC1)*(pmtD=RC4)*(pmtQ=RC17)*(pmtR=R1C26),0))"
    
    MyRange.Value = MyRange.Value 'Convert formulas to values
    
End Sub

 

[Sthrncali]

I get the error message "Unable to set the formula array property of the range class.

Here is the code. The Names have been added to the workbook and do show up in the Names list. Also the formula entered directly into the cell by me evaluates without error.

    Set MyRange = Sheet10.Range("Z2:AQ2")
    MyRange.FormulaArray = "=INDEX(pmtU,MATCH(1,(pmtA=RC1)*(pmtD=RC4)*(pmtQ=RC17)*(pmtR=R1C26),0))"

Here is the formula I tested directly in the cell that works using the added Names:

=INDEX(pmtU,MATCH(1,(pmtA=A2)*(pmtD=D2)*(pmtQ=Q2)*(pmtR=AB$1),0))