Hi Jim,

Thanks for your help. I'll give it a try changing "Input Information" to a shorter name.

Jim, when you say "inefficient" formula, what do you really mean? Excel slows down for any reason? Or any other explanation? On this particular case I tried before INDEX&MATCH but I thought it was a bit more complicated formula... Any further help will be very much appreciated.

"Jim Rech" wrote:

The problem is that the formula is too long. It exceeds 255 characters. If
you change the name of the sheet from Input Information to, say "X" as a
test, you should find that the macro works.

This is a very inefficient formula, btw. You should use MATCH to find the
right row and then INDEX of the range you want (X6:X3000 I believe) to find
the value. If you do other vlookups based on the MAX of that range you
should pull that piece and the MATCH result out into separate cells.

--
Jim Rech
Excel MVP
"litos_aldovea" wrote in message
...
| Hi all,
|
| I am new to VBA, I am developing a dashboard in Excel. There is a Combobox
where you can change from one store to another, and the dashboard refreshes
all the information related to the new store.
|
| When you change to another store, certain CELLS need to be "rekeyed", to
do so, I have a SUB PROCEDURE that rewrite the contents in this CELLS puting
back some formulas.
|
| Well, my problem is that the following CODE should replace the content in
F83 and input the array formula that is written btween the QUOTES. I tested
the formula in excel (outside VBA) and it works retrieving the DATA that I
need.
|
| But, why when I run the macro VBA gives me an error? Why?
|
| Range("F83").FormulaArray = "=IF(ISERROR(VLOOKUP(MAX(IF('Input
Information'!$A$6:$A$3000=DOOR!$C$11,'Input
Information'!$B$6:$B$3000,0)),'Input
Information'!$B$6:$EK$3000,23,FALSE)),0,VLOOKUP(MA X(IF('Input
Information'!$A$6:$A$3000=DOOR!$C$11,'Input
Information'!$B$6:$B$3000,0)),'Input Information'!$B$6:$EK$3000,23,FALSE))"
|
| Thanks a lot in advance,
| Carlos Lopez.
|
|