Array formula, variable and VBA

[Padawan]

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.

 

[mvptomlinson]

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:

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]

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.