why array formula ask me to pick up the file again?

[kenneth]

Hi,
I have the following VB codes to put an array formula in the selected cell.

Selection.FormulaArray = _
"=INDEX('[allresFileName]Sheet0'!$r$10:$r$" & last_row & ",MATCH(a28&d28&e28&g28,'[allresFileName]Sheet0'!$a$10:$a$" & last_row & "&'[allresFileName]Sheet0'!$f$10:$f$" & last_row & "&'[allresFileName]Sheet0'!$g$10:$g$" & last_row & "&'[allresFileName]Sheet0'!$i$10:$i$" & last_row & ",0))"

allresFileName was predefined.
last_row was predefined.

The issue is when I run the Macro, it still ask me to pick up the allresFile.

The Macro works but very annoying.

Can anyone help.

Thank you and have a great day.

 

[Hui]

Kenneth

Firstly, Welcome to the Chandoo.org Forums

Your line is taking the whole Index() function in as text, but you want to use the allResFileName as a variable

Just make the changes as marked below

Selection.FormulaArray = _
"=INDEX('[" & allresFileName & "]Sheet0'!$r$10:$r$" & last_row & ",MATCH(a28&d28&e28&g28,'[" & allresFileName & "]Sheet0'!$a$10:$a$" & last_row & "&'[" & allresFileName & "]Sheet0'!$f$10:$f$" & last_row & "&'[" & allresFileName & "]Sheet0'!$g$10:$g$" & last_row & "&'[" & allresFileName & "]Sheet0'!$i$10:$i$" & last_row & ",0))"

as you have done with last_row