Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("F83").FormulaArray
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. | | |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |