# Array formula, variable and VBA

##### Active Member
I thought I posted this, but I guess it went to cyberland...

Window XP; Excel 2003

Greetings Board.

A simple problem I hope. I need to post an array formula through VBA. It includes a named last row.

The VBA =

Range("X5").FormulaArray = "=IF(ISNA(INDEX(\$T\$6:\$T\$663,MATCH(V6&W6,\$R\$6:\$R\$663&\$S\$6:\$S\$663,0))="#N/A"),0,INDEX(\$T\$6:\$T\$663,MATCH(V6&W6,\$R\$6:\$R\$663&\$S\$6:\$S\$663,0)))"

I would like to replace the number 663 with the variable TLR.

I'm having some trouble in that I can't even get the formula to run through the VBA even without the TLR variable. So I guess I need a little help with both the array formula and adding the variable into the array.

As always, THANK YOU.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### mvptomlinson

##### Well-known Member
John, one question before providing a possible solution..

Why are you using ISNA *and* checking to see if the formula results in the #N/A error? That's the whole point of the ISNA function. I would suggest:
Code:
``Range("X5").FormulaArray = "=IF(ISNA(INDEX(\$T\$6:\$T\$" & TLR & ",MATCH(V6&W6,\$R\$6:\$R\$" & TLR & "&\$S\$6:\$S\$" & TLR & ",0))),0,INDEX(\$T\$6:\$T\$" & TLR & ",MATCH(V6&W6,\$R\$6:\$R\$" & TLR & "&\$S\$6:\$S\$" & TLR & ",0)))"``

##### Active Member
A major thank you for clarifying my confusing formula.

I apologize for not getting back to you sooner; I evidently missed the email alert.

I finally figuered out what I think was the key to this. I'll revew it now for your input and for other poster's information.

It finally sank in.

First, I kept on the formula without the variable until I could get it to work. I re-read one of my books and found out / remembered array formulas had to entered in R1C1 format.

Once I got that to work, I reviewed some of my other code and came to realize this. A simple formula to use (at least for me) was to go to the point I wanted to enter a variable.
Type a double quote there". Next a space, then an ampersand &. Another space, then my variable, space, ampersand, space, double quote. When done, it matches exactly the sample you so graciously provided. I used this technique and was able to complete the whole equation.

Again, I want to THANK YOU for the reply. The R1C1 format was a little tough for the array formula, but everything is working now.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,148
Messages
5,835,680
Members
430,375
Latest member
datdog22

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