Array formula, variable and VBA

Padawan

Active Member
Joined
Apr 9, 2002
Messages
392
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
Joined
Mar 10, 2008
Messages
2,638
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)))"
 

Padawan

Active Member
Joined
Apr 9, 2002
Messages
392
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top