| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this not work?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does this not work?
http://www.cpearson.com/excel/nested.htm
-- Regards, Tom Ogilvy "Peterke" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does this not work?
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 "Tom Ogilvy" schreef in bericht ... http://www.cpearson.com/excel/nested.htm -- Regards, Tom Ogilvy "Peterke" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does this not work?
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 "Peterke" wrote in message ... 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 "Tom Ogilvy" schreef in bericht ... http://www.cpearson.com/excel/nested.htm -- Regards, Tom Ogilvy "Peterke" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does this not work?
Thank you again Tom,
this helps me alot. Regards, peter "Tom Ogilvy" schreef in bericht ... 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 "Peterke" wrote in message ... 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 "Tom Ogilvy" schreef in bericht ... http://www.cpearson.com/excel/nested.htm -- Regards, Tom Ogilvy "Peterke" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
| flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
| Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Hybrid Mode
