• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro help needed! How to put text in formula array?

K

Kevon7878

New Member
I have the following Index-Match Match Array Formula which I want to be able to put in VBA in a cell. However when I use the Formulaarray function, it results in a syntax error. The problem seems to be in the last match criteria which I put in bold text which is for me to identify the column number to pick up a price from. When I substitute the bold text with a number it works but I need to leave it as a variable so that it goes through the whole table row by row and does a match process with the month and year from another price table, e.g Jan-15 will be matched in the column number in the price spreadsheet and price is picked up.

Can anyone help me?

=INDEX(Prices!$A$2:$R$230, MATCH(1,(Prices!$A$2:$A$230=Sheet1!F2)*(Prices!$B$2:$B$230=Sheet1!E2)*(LEFT(Prices!$D$2:$D$230,1)=Sheet1!BB2),0),MATCH(TEXT(D2,"mmm-yy"),TEXT(Prices!$A$1:$R$1,"mmm-yy"),0))
 
Hi ,

The following statement works correctly :

ActiveCell.FormulaArray = "=INDEX(Prices!$A$2:$R$230, MATCH(1,(Prices!$A$2:$A$230=Sheet1!F2)*(Prices!$B$2:$B$230=Sheet1!E2)*(LEFT(Prices!$D$2:$D$230,1)=Sheet1!BB2),0),MATCH(TEXT(D2,""mmm-yy""),TEXT(Prices!$A$1:$R$1,""mmm-yy""),0))"

Of course , instead of the ActiveCell keyword , you can use what ever is relevant to your code.

If you are using a different kind of formula using variables instead of static references , it is possible your formula length may be exceeding the limit for the FormulaArray method , which is 255 characters.

Narayan
 
NARAYANK991 said:
Hi ,

The following statement works correctly :

ActiveCell.FormulaArray = "=INDEX(Prices!$A$2:$R$230, MATCH(1,(Prices!$A$2:$A$230=Sheet1!F2)*(Prices!$B$2:$B$230=Sheet1!E2)*(LEFT(Prices!$D$2:$D$230,1)=Sheet1!BB2),0),MATCH(TEXT(D2,""mmm-yy""),TEXT(Prices!$A$1:$R$1,""mmm-yy""),0))"

Of course , instead of the ActiveCell keyword , you can use what ever is relevant to your code.

If you are using a different kind of formula using variables instead of static references , it is possible your formula length may be exceeding the limit for the FormulaArray method , which is 255 characters.

Narayan
Click to expand...

Hi Narayan, yes silly me. Just an issue of adding an extra "" that I was missing causing the Syntax error. Thank you for that
 
You must log in or register to reply here.
Back
Top