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

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

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
(E-Mail Removed)
"MichaelDavid" <(E-Mail Removed)> wrote in message
news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> 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

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
> (E-Mail Removed)
> "MichaelDavid" <(E-Mail Removed)> wrote in message
> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> > 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
>
>

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
(E-Mail Removed)
"MichaelDavid" <(E-Mail Removed)> wrote in message
news:8B69334F-0F54-4F78-89E9-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "MichaelDavid" <(E-Mail Removed)> wrote in message
>> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
>> > 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
>>
>>

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
> (E-Mail Removed)
> "MichaelDavid" <(E-Mail Removed)> wrote in message
> news:8B69334F-0F54-4F78-89E9-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "MichaelDavid" <(E-Mail Removed)> wrote in message
> >> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>

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
> > (E-Mail Removed)
> > "MichaelDavid" <(E-Mail Removed)> wrote in message
> > news:8B69334F-0F54-4F78-89E9-(E-Mail Removed)...
> > > 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
> > >> (E-Mail Removed)
> > >> "MichaelDavid" <(E-Mail Removed)> wrote in message
> > >> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> >
> >

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" <(E-Mail Removed)> wrote in message news:8B69334F-0F54-4F78-89E9-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "MichaelDavid" <(E-Mail Removed)> wrote in message
> >> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> >> > 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
> >>
> >>

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" <(E-Mail Removed)> wrote in message news:8B69334F-0F54-4F78-89E9-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "MichaelDavid" <(E-Mail Removed)> wrote in message
> >> news:E51F7091-8E93-47B6-9AA3-(E-Mail Removed)...
> >> > 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
> >>
> >>