Nov 10, 2011
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.
View 2 Replies
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
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)
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.