# Help converting formula to VBA code

#### Sthrncali

##### Board Regular
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.

Last edited:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Here's a start...

Code:
``````Sub test()

Dim MyRange As Range

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``````

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.

Code:
``````    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))

Replies
5
Views
233
Replies
21
Views
558
Replies
1
Views
113
Replies
3
Views
154
Replies
9
Views
704

### Forum statistics

1,203,435
Messages
6,055,362
Members
444,781
Latest member
rishivar ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back