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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

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