Why does this not work?

[Peterke]

Hi,

i'm having a little problem with vba(again), and could use some help.
The following formula gives "error 1004"
It's just an "IF" formula, so why does it not work?
Please advice.

Regards,
Peter

Range("H14").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-1
1]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!
R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",She
et6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8""
,Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Shee
t10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3
]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select

 

[Tom Ogilvy]

http://www.cpearson.com/excel/nested.htm

--
Regards,
Tom Ogilvy

Hi,

i'm having a little problem with vba(again), and could use some help.
The following formula gives "error 1004"
It's just an "IF" formula, so why does it not work?
Please advice.

Regards,
Peter

Range("H14").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-11]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",Sheet6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8"",Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Sheet10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3
]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select

 

[Peterke]

Thx Tom,

I've looked at it, but still do'nt get it, sorry.
I got the idea of nesting, that's not the problem anymore.
But how do i name the formula? I'm not familliar with that.
Is it something like:
Name formula. OneToSix or
Name formula= OneToSix
or something else?

Regards,
Peter

http://www.cpearson.com/excel/nested.htm

--
Regards,
Tom Ogilvy

Hi,

i'm having a little problem with vba(again), and could use some help.
The following formula gives "error 1004"
It's just an "IF" formula, so why does it not work?
Please advice.

Regards,
Peter

Range("H14").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-11]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",Sheet6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8"",Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Sheet10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3

]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select

 

[Tom Ogilvy]

thisworkbook.Names.Add Name:="OneToSix", ReferstoR1C1:=" a formula string
starting with ="

However, Chip was just using Named formulas to simplify things. You can
replace the two names with the actual formulas.

a pseudocode example:
ActiveCell.FormulaR1C1 =
"=if(If(if(if(if(if())))),If(if(if(if(if())))),If(If(If(if(if()))))"

vice
ActiveCell.FormulaR1C1 = "if(OneToSix,Onetosix,SeventoThirteen)"

Often, when having so many conditions, another method can be used. Examples
would be Vlookup or Choose. I can't say for your formula.

--
Regards,
Tom Ogilvy

Thx Tom,

I've looked at it, but still do'nt get it, sorry.
I got the idea of nesting, that's not the problem anymore.
But how do i name the formula? I'm not familliar with that.
Is it something like:
Name formula. OneToSix or
Name formula= OneToSix
or something else?

Regards,
Peter

http://www.cpearson.com/excel/nested.htm

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-11]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",Sheet6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8"",Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Sheet10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3

]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select

 

[Peterke]

Thank you again Tom,

this helps me alot.

Regards,
peter

thisworkbook.Names.Add Name:="OneToSix", ReferstoR1C1:=" a formula string
starting with ="

However, Chip was just using Named formulas to simplify things. You can
replace the two names with the actual formulas.

a pseudocode example:
ActiveCell.FormulaR1C1 =
"=if(If(if(if(if(if())))),If(if(if(if(if())))),If(If(If(if(if()))))"

vice
ActiveCell.FormulaR1C1 = "if(OneToSix,Onetosix,SeventoThirteen)"

Often, when having so many conditions, another method can be used. Examples
would be Vlookup or Choose. I can't say for your formula.

--
Regards,
Tom Ogilvy

Thx Tom,

I've looked at it, but still do'nt get it, sorry.
I got the idea of nesting, that's not the problem anymore.
But how do i name the formula? I'm not familliar with that.
Is it something like:
Name formula. OneToSix or
Name formula= OneToSix
or something else?

Regards,
Peter

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-11]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",Sheet6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8"",Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Sheet10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3

]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select