| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Greetings! Does anyone know the best way of understanding extremely
complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
By the way, can Excel 2007 handle 14 nested IFs like this? No diagnostic
messages are ever displayed when I execute the macro containing this code. This particular macro is executed approx. 30 times a week. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
You have to break this function down, it is clearly unmanageable.
Break it down into helper cells and use the results in the next part. -- __________________________________ HTH Bob "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Did you look at VBE help for SELECT CASE to see the proper syntax
Did I not suggest NOT using r1c1 style if you want this to be easy to follow and change. Select Case Statement Executes one of several groups of statements, depending on the value of an expression. Syntax Select Case testexpression [Case expressionlist-n [statements-n]] ... [Case Else [elsestatements]] End Select then apply the select -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Greetings, Don. I appreciate your help. Unfortunately I wasn't able to figure
out how to use a Select Case in the propagation of a formula throughout a range. When you have some free time, please give me an example of using a Select Case in the propagation of a formula through a range (Such as N2:N4000). (I guess the key might be in finding an alternate way to propagate a formula throughout a range without using R1C1 format.) Thanks a million! -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: Did you look at VBE help for SELECT CASE to see the proper syntax Did I not suggest NOT using r1c1 style if you want this to be easy to follow and change. Select Case Statement Executes one of several groups of statements, depending on the value of an expression. Syntax Select Case testexpression [Case expressionlist-n [statements-n]] ... [Case Else [elsestatements]] End Select then apply the select -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Greetings again. By the way, I have successfully used Select Case in
conjunction with propagating a formula throughout a range. Here is the way it appears in my VBA code (please ignore the line numbers): 274 Range("AB1").FormulaR1C1 = " " 275 Select Case Response Case Is = vbYes 276 Range("AB2:AB" & LstRowData).FormulaR1C1 = _ "=IF(RC[-25]=""Issuer name: "","""", " & _ "IF(AND(RC[-1]+RC[+4]=R2C37,RC[-3]=R2C39,RC[-5](30000+11000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)9),""INVESTGT""," & _ "IF(AND(RC[-1]+RC[+4]=R3C37,RC[-3]=R3C39,RC[-5](30000+10000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)7),""INV POS ""," & _ "IF(LEFT(R[-1]C,3)=""INV"",""SELL"",""""))))" 277 Range("H1") = "Bonanza" 278 Case Is = vbNo 279 Range("AB2:AB" & LstRowData).FormulaR1C1 = _ "=IF(RC[-25]=""Issuer name: "","""", " & _ "IF(AND(RC[-1]+RC[+4]=R4C37,RC[-3]=R4C39,RC[-5](36000+11000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)9),""INVESTGT""," & _ "IF(OR(RC[-5]TtlPurchThrshld,AND(RC[-1]+RC[+4]=R5C37,RC[-3]=R5C39,RC[-5](30000+10000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)7)),""INV POS ""," & _ "IF(LEFT(R[-1]C,3)=""INV"",""SELL"",""""))))" ' ABOVE WAS: "IF(AND(RC[-1]+RC[+4]=R4C37,RC[-3]=R4C39,RC[-5] (20000+9000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)5),""INVESTGT""," & _ ' ABOVE WAS: "IF(OR(RC[-5]TtlPurchThrshld,AND(RC[-1]+RC[+4]=R5C37,RC[-3]=R5C39,RC[-5](20000+8000*R1C7),(RC[-5]-RC[-6])/(-RC[-6]+1)4)),""INV POS ""," & _ Range("H1") = "Regular" 280 Case Is = vbCancel 281 GoTo ExitMain 282 End Select But how to use and apply Select Case to do the propagation of the formula through the range escapes me. I hope you have some suggestions. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings, Don. I appreciate your help. Unfortunately I wasn't able to figure out how to use a Select Case in the propagation of a formula throughout a range. When you have some free time, please give me an example of using a Select Case in the propagation of a formula through a range (Such as N2:N4000). (I guess the key might be in finding an alternate way to propagate a formula throughout a range without using R1C1 format.) Thanks a million! -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: Did you look at VBE help for SELECT CASE to see the proper syntax Did I not suggest NOT using r1c1 style if you want this to be easy to follow and change. Select Case Statement Executes one of several groups of statements, depending on the value of an expression. Syntax Select Case testexpression [Case expressionlist-n [statements-n]] ... [Case Else [elsestatements]] End Select then apply the select -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Thanks a million! Now the biggie is converting the code to be used in the
formula from R1C1 format to structured code. After I file my taxes (due Oct 15), I will try your solution. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Greetings again! I just hope that propagating a function call through the
range doesn't cause execution time to take a serious hit--the entire program currently takes about an hour to complete. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
On Sun, 5 Oct 2008 10:56:11 -0700, MichaelDavid
wrote: By the way, can Excel 2007 handle 14 nested IFs like this? No. 7 max. I didnt try to analize the Ifs. Are you currently runnibg this code in Excel? Don Guillett and Bob Phillips have given you the best advice you can get. Chuck No diagnostic messages are ever displayed when I execute the macro containing this code. This particular macro is executed approx. 30 times a week. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
--the entire program
currently takes about an hour to complete. Did you change calculation to Manual? It shouldn't take more than a few seconds. You can also use... Range("N2:N4000").Formula = "=MyFx(A2,C2,E2)" Without filling down. No ones asked - outa sheer fear no doubt - but what is the reason for the formula? Can you not just program the values to the desired cells? I remember when I tried to use these things when I was experienced with the XL interface, but very green using VBA. I kept using what I knew best with XL formulae, and pulling that into my code constructs. What a mess I created! To this day I can not figure out what the hell most of the code I wrote then, supposed to do! Unfortunately, what you posted today will only show to an experienced programmer, that it was written by someone that is not at all familiar with VBA. Two really good features you can use, is to filter your data or use the Advanced Filter function. And Pivot Tables. (is that 3?) Advanced filter will take a zillion criteria and zap the results where ever you like. Just edit the recorded code, so the output is on another sheet, as well as the Criteria and Copyto range. Another way to reduce that 'IF' formula, is to use either Choose or Lookup. Check them out from the Help files. Lastly the best advice you can get is: Give up smoking! Ok you don't smoke then: Eat a balanced diet and exercise regularly. You already do; Well, great advice wasn't it? Regards Robert McCurdy "MichaelDavid" wrote in message ... Greetings again! I just hope that propagating a function call through the range doesn't cause execution time to take a serious hit--the entire program currently takes about an hour to complete. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Hi Robert,
To clear up a misunderstanding, the code under examination, appears in a hugh program of about 36000 Macro Scheduler instructions and 8000 Excel VBA instructions. Essentially the program visits various web sites and, for each set of dates (from another worksheet), gathers data, copies this data to a new Excel Worksheet, analyzes the data, and makes recommendations. The basic cause of the one hour processing time is the number of data sets gathered and the slowness of the internet web sites to respond to requests for data (these requests being made by the Macro Scheduler macros). In order to get to max speed, each of the main Excel Subroutines begins with: With Application .ScreenUpdating = False .Calculation = xlCalculationManual ' Inhibit Automatic Worksheet Updating .DisplayAlerts = False End With After the data gathered from the internet web sites is copied to an Excel worksheet, one of the Excel subroutines analyzes this data row by row for up to about 4000 rows of data. Based upon key text in each of about six columns of the worksheet, the code under examination decides how to continue propagation of the values computed by the formula down through column N say. This is done for each of the about six columns of interest. Formulas cannot be prestored in the worksheet because they would be overwritten when the internet data is pasted in, and it isn't even known how many rows of data there will be until after the paste operation. The first thing the main Excel procedure does is count the number of rows of data (LstRowData).. When I was a beta tester for Office 12, I vigorously protested to Microsoft about the difficulty of understanding and maintaining this kind of R1C1 logic having a series of nested Ifs. Microsoft said they didn't have time to address the issue for this release. Thanks again for your response -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Robert McCurdy" wrote: --the entire program currently takes about an hour to complete. Did you change calculation to Manual? It shouldn't take more than a few seconds. You can also use... Range("N2:N4000").Formula = "=MyFx(A2,C2,E2)" Without filling down. No ones asked - outa sheer fear no doubt - but what is the reason for the formula? Can you not just program the values to the desired cells? I remember when I tried to use these things when I was experienced with the XL interface, but very green using VBA. I kept using what I knew best with XL formulae, and pulling that into my code constructs. What a mess I created! To this day I can not figure out what the hell most of the code I wrote then, supposed to do! Unfortunately, what you posted today will only show to an experienced programmer, that it was written by someone that is not at all familiar with VBA. Two really good features you can use, is to filter your data or use the Advanced Filter function. And Pivot Tables. (is that 3?) Advanced filter will take a zillion criteria and zap the results where ever you like. Just edit the recorded code, so the output is on another sheet, as well as the Criteria and Copyto range. Another way to reduce that 'IF' formula, is to use either Choose or Lookup. Check them out from the Help files. Lastly the best advice you can get is: Give up smoking! Ok you don't smoke then: Eat a balanced diet and exercise regularly. You already do; Well, great advice wasn't it? Regards Robert McCurdy "MichaelDavid" wrote in message ... Greetings again! I just hope that propagating a function call through the range doesn't cause execution time to take a serious hit--the entire program currently takes about an hour to complete. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Hi Chuck:
In Excel 2007, 64 levels of nesting are allowed. And that is why my 14 levels of nesting works perfectly, and, as far as I know, is completely debugged. But I searched the Microsoft Knowledge Base for a statement of this fact, and apparently this information is not there. If it is there, what should I have used for my search terms? Thanks for your help. Sincerely, Michael D Fitzpatrick -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Chuck" wrote: On Sun, 5 Oct 2008 10:56:11 -0700, MichaelDavid wrote: By the way, can Excel 2007 handle 14 nested IFs like this? No. 7 max. I didnt try to analize the Ifs. Are you currently runnibg this code in Excel? Don Guillett and Bob Phillips have given you the best advice you can get. Chuck No diagnostic messages are ever displayed when I execute the macro containing this code. This particular macro is executed approx. 30 times a week. |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Hi Chuck:
According to the book "Excel 2007 - Beyond the Manual" by Helen Dixon, p 189: "TIP Excel 2007 can work with bigger formulas than ever before and can now deal with 8,192 characters and 64 levels of nesting compared to 1,024 characters and 7 levels of nesting in Excel 2003." The link to an online page 189: http://books.google.com/books?id=is2...um=2&ct=result May you have a most blessed day! Sincerely, Michael Fitzpatrick "Chuck" wrote: On Sun, 5 Oct 2008 10:56:11 -0700, MichaelDavid wrote: By the way, can Excel 2007 handle 14 nested IFs like this? No. 7 max. I didnt try to analize the Ifs. Are you currently runnibg this code in Excel? Don Guillett and Bob Phillips have given you the best advice you can get. Chuck No diagnostic messages are ever displayed when I execute the macro containing this code. This particular macro is executed approx. 30 times a week. |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Hi Robert!
I am somewhat inexperienced with functions, so I tried a simple test case to see if I could implement your suggestion. Here is what I came up with: Option Explicit Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2([AB2], [AB3], [AC3])" ' RANGE 1 ' Range("AC2:AC10").Formula = "=SetAC2(""SELL"", ""SELL"", ""SELL"")" ' RANGE 2 [AB3] = "SELL" Exit Sub End Sub Public Function SetAC2(Locn1, Locn2, Locn3) As String If Locn1 = "" Then SetAC2 = "" ElseIf Locn2 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" Else: SetAC2 = Locn1 End If End Function When I executed the above procedure with what I refer to as "Range 1" (commenting out "Range 2"), I get the following: "Run-time error '1004': Application-defined or object defined error." When I click debug, it highlights Range("AC2:AC10").Formula = "=SetAC2([AB2], [AB3], [AC3])" When I commented out Range 1 and tried with Range 2, the procedure executes but fills the range AC2:AC10 with: #NAME?. Looking at AC2, it contains: =SetAC2("SELL", "SELL", "SELL"). Any help or suggestions for debugging will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Robert McCurdy" wrote: --the entire program currently takes about an hour to complete. Did you change calculation to Manual? It shouldn't take more than a few seconds. You can also use... Range("N2:N4000").Formula = "=MyFx(A2,C2,E2)" Without filling down. No ones asked - outa sheer fear no doubt - but what is the reason for the formula? Can you not just program the values to the desired cells? I remember when I tried to use these things when I was experienced with the XL interface, but very green using VBA. I kept using what I knew best with XL formulae, and pulling that into my code constructs. What a mess I created! To this day I can not figure out what the hell most of the code I wrote then, supposed to do! Unfortunately, what you posted today will only show to an experienced programmer, that it was written by someone that is not at all familiar with VBA. Two really good features you can use, is to filter your data or use the Advanced Filter function. And Pivot Tables. (is that 3?) Advanced filter will take a zillion criteria and zap the results where ever you like. Just edit the recorded code, so the output is on another sheet, as well as the Criteria and Copyto range. Another way to reduce that 'IF' formula, is to use either Choose or Lookup. Check them out from the Help files. Lastly the best advice you can get is: Give up smoking! Ok you don't smoke then: Eat a balanced diet and exercise regularly. You already do; Well, great advice wasn't it? Regards Robert McCurdy "MichaelDavid" wrote in message ... Greetings again! I just hope that propagating a function call through the range doesn't cause execution time to take a serious hit--the entire program currently takes about an hour to complete. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Best Way to Understand Extremely Dense Nested Ifs in Formulas
Greetings again. I got rid of the "Application-defined or object defined
error" by changing the code as follows (I removed the brackets from AB2, AB3, and AC3 in the Formula): Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2(AB2, AB3, AC3)" [AB3] = "SELL" Exit Sub End Sub Public Function SetAC2(Locn1, Locn2, Locn3) As String If Locn1 = "" Then SetAC2 = "" ElseIf Locn2 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" Else: SetAC2 = Locn1 End If End Function But the range AC2:AC10 is still filled with: #NAME?, and the tooltip says: "The formula contains unrecognized text." -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Hi Robert! I am somewhat inexperienced with functions, so I tried a simple test case to see if I could implement your suggestion. Here is what I came up with: Option Explicit Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2([AB2], [AB3], [AC3])" ' RANGE 1 ' Range("AC2:AC10").Formula = "=SetAC2(""SELL"", ""SELL"", ""SELL"")" ' RANGE 2 [AB3] = "SELL" Exit Sub End Sub Public Function SetAC2(Locn1, Locn2, Locn3) As String If Locn1 = "" Then SetAC2 = "" ElseIf Locn2 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" ElseIf Locn3 = "SELL" Then SetAC2 = "SELL" Else: SetAC2 = Locn1 End If End Function When I executed the above procedure with what I refer to as "Range 1" (commenting out "Range 2"), I get the following: "Run-time error '1004': Application-defined or object defined error." When I click debug, it highlights Range("AC2:AC10").Formula = "=SetAC2([AB2], [AB3], [AC3])" When I commented out Range 1 and tried with Range 2, the procedure executes but fills the range AC2:AC10 with: #NAME?. Looking at AC2, it contains: =SetAC2("SELL", "SELL", "SELL"). Any help or suggestions for debugging will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Robert McCurdy" wrote: --the entire program currently takes about an hour to complete. Did you change calculation to Manual? It shouldn't take more than a few seconds. You can also use... Range("N2:N4000").Formula = "=MyFx(A2,C2,E2)" Without filling down. No ones asked - outa sheer fear no doubt - but what is the reason for the formula? Can you not just program the values to the desired cells? I remember when I tried to use these things when I was experienced with the XL interface, but very green using VBA. I kept using what I knew best with XL formulae, and pulling that into my code constructs. What a mess I created! To this day I can not figure out what the hell most of the code I wrote then, supposed to do! Unfortunately, what you posted today will only show to an experienced programmer, that it was written by someone that is not at all familiar with VBA. Two really good features you can use, is to filter your data or use the Advanced Filter function. And Pivot Tables. (is that 3?) Advanced filter will take a zillion criteria and zap the results where ever you like. Just edit the recorded code, so the output is on another sheet, as well as the Criteria and Copyto range. Another way to reduce that 'IF' formula, is to use either Choose or Lookup. Check them out from the Help files. Lastly the best advice you can get is: Give up smoking! Ok you don't smoke then: Eat a balanced diet and exercise regularly. You already do; Well, great advice wasn't it? Regards Robert McCurdy "MichaelDavid" wrote in message ... Greetings again! I just hope that propagating a function call through the range doesn't cause execution time to take a serious hit--the entire program currently takes about an hour to complete. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dana DeLouis" wrote: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" also: Range("N2:N" & LstRowData).FormulaR1C1 = Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula. Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down. This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells. Hope this simple example helps out. Sub MyMainRoutine() 'Have your formula point to all the input cells Range("N2").Formula = "=MyFx(A2,C2,E2)" 'When your formula is correct, copy it down. Range("N2:N13").FillDown End Sub Function MyFx(ColA, ColC, ColE) Dim Answer '// Do all your calculations here '// where they can be documented. Select Case ColC Case "F3 Start Month" Answer = 999 Case Else Answer = 0 End Select '//Return solution MyFx = Answer End Function -- Dana DeLouis "MichaelDavid" wrote in message ... Greetings Don: I tried your suggestion, but Excel VBA does not seem to like such constructions as: Range("N2:N" & LstRowData).FormulaR1C1 = _ "=Select Case (RC[-2])" Case Is = "F3 Start Month": RC [-2] = 999 Case Else RC [-2] = 0 End Select Although the VBE allows me to enter the above code, when I try to execute it, I get the message: "Compile error: Case without Select Case". Please keep in mind that I am trying to enter formulas throughout the range from N2 through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Don Guillett" wrote: I agree. Suggest you get away from r1c1 and have a look at VBE help for SELECT CASE Also, Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" can possibly be cells(1,23)=0 -- Don Guillett Microsoft MVP Excel SalesAid Software "MichaelDavid" wrote in message ... Greetings! Does anyone know the best way of understanding extremely complicated Nested IFs in formulas in VBA? Consider the following code which I believe I completely debugged over a year ago: Application.Goto Reference:="R1C23:R1C23" ActiveCell.FormulaR1C1 = "0.0" Range("W2:W" & LstRowData).FormulaR1C1 = _ "=IF(RC[-20]=""XIssuer name: "",0.0, " & _ "IF(AND(RC[-12]<""10 - Acquisition or disposition in the public market "", RC[-12]<""11 - Acquisition or disposition carried out privately "", RC[-12]<""30 - Acquisition or disposition under a purchase/ownership plan ""),R[-1]C, " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership :""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership :""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _ "IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership :""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), " & _ "IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))" I have found this code extremely difficult to understand and modify. Every time I want to make a small change in one of the deeper nested IFs, I can count on it taking over 30 minutes. Is there a better way of expressing the above code? A better and easier to understand way of accomplishing the function of the above code? I am open to any and all suggestions. Thanks in advance for your help. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested formulas | Excel Worksheet Functions | |||
| NESTED OR FORMULAS | Excel Discussion (Misc queries) | |||
| Need help with IF nested formulas | Excel Discussion (Misc queries) | |||
| I need to understand how the IRR, NPV and FPV formulas work? Thks | Excel Worksheet Functions | |||
| nested formulas | Excel Discussion (Misc queries) | |||
Linear Mode
