Thread
:
Best Way to Understand Extremely Dense Nested Ifs in Formulas
View Single Post
#
8
Posted to microsoft.public.excel.programming
MichaelDavid
external usenet poster
Posts: 100
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
Reply With Quote
MichaelDavid
View Public Profile
Find all posts by MichaelDavid