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.
|
|

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.
|
|

If you have just one lookup formula like this it's not a big deal. But I've
seen spreadsheets where people have several hundred. They can take hours to
calculate but that can be reduced to under a minute. I've seen it happen.

When a formula references a range Excel has to make sure that all the
formulas in that range have been calculated and are up to date. Of course
if there are formulas that those formulas refer to then they have to be
checked, etc. Your formula references a range about 36 columns wide and
3000 rows deep. That's a lot of cells. But you know when you wrote the
formula that you were only interested in one column (#23) in that range. So
all the checking of the other 29 columns is a total waste.

Another inefficiency (if you do it a lot of times) is looking up the max of
a range. Say have a hundred formulas that each determine MAX(Some big
range). It's far better to have one cell that does that alone and have the
formulas that need this number reference that one cell.

Your formula does TWO huge identical Vlookups. It's better to do it once in
a cell by itself and reference that twice.

How complicated a formula looks does not indicate how efficient it is.
Doing a Match on the first column and an Index into column 23 may look more
complicated, but it is far faster than your Vlookup.

--
Jim Rech
Excel MVP
"litos_aldovea" wrote in message
...
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.
|
|