Only a genius can help with this 7 Nested If that doesn't fill down
 

I'm using MS XL 2000

App.Range("AQ2").Formula = "=IF(AT2 = 500,MROUND(AI2,500),IF(AT2 =
5,MROUND(AI2,5),IF(AT2=10,MROUND(AI2,10),IF(AT2 =
25,MROUND(AI2,25),IF(AT2 = 100,MROUND(AI2,100),IF(AT2 =
200,MROUND(AI2,200),IF(AT2 =
364,MROUND(AI2,364),ROUNDDOWN(AI2,0))))))))"
App.Range("AQ2:AQ" + Total).FillDown

'where TOTAL = record count of a query

I get #Name# in the cells that I want to be calculated.

My formula doesn't fill correctly. I've tried switching the automatic
and manual and iteration and calculate before saving and they do not
work. I've also tried formatting the number. Any ideas?

Only a genius can help with this 7 Nested If that doesn't fill down
 

shorten that to:

=IF(ISERROR(MATCH(AT2,{0;5;10;25;100;200;364},0)),
ROUNDDOWN(AI2,0),MROUND(AI2,AT2))

Also note that MROUND is a function from
the Analysis Toolpak . (hence the NAME error?)

If that is a problem: try Floor or Ceiling instead.
but beware they dont like negative amounts.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CondtllyFrmttd wrote :

"=IF(AT2 = 500,MROUND(AI2,500),IF(AT2 =
5,MROUND(AI2,5),IF(AT2=10,MROUND(AI2,10),IF(AT2 =
25,MROUND(AI2,25),IF(AT2 = 100,MROUND(AI2,100),IF(AT2 =
200,MROUND(AI2,200),IF(AT2 =
364,MROUND(AI2,364),ROUNDDOWN(AI2,0))))))))"

Only a genius can help with this 7 Nested If that doesn't fill down
 

Both my formula and yours work. THe only catch is that I have to
manually put my cursor in the actual formula and hit enter. Why the
hell does it do this??!?!?!?!?!? I still get #Name?

Only a genius can help with this 7 Nested If that doesn't fill down
 

name error due to ATP function.
are you sure the Analysis Toolpak Addin is loaded?


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CondtllyFrmttd wrote :

Both my formula and yours work. THe only catch is that I have to
manually put my cursor in the actual formula and hit enter. Why the
hell does it do this??!?!?!?!?!? I still get #Name?

Only a genius can help with this 7 Nested If that doesn't fill down
 

I have the analysis tookpak addin is loaded. I believe that the
formatting was lost somewhere down the line so i must double check all
46 columns and the queries' properties. Thanks for all you help though.

Only a genius can help with this 7 Nested If that doesn't fill down
 

I've even tried using the sendkey send keys function and it still does
not like to be calculated using code! I've literally tried everything,
volatility, app.calculate, all that B.S. This crap is still not
working!!