Best Way to Understand Extremely Dense Nested Ifs in Formulas

M

MichaelDavid

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
 
M

MichaelDavid

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 said:
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
Click to expand...
 
D

Don Guillett

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"
Click to expand...
can possibly be

cells(1,23)=0

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
MichaelDavid said:
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
Click to expand...
 
B

Bob Phillips

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.
 
M

MichaelDavid

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 said:
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"
Click to expand...
can possibly be

cells(1,23)=0

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
MichaelDavid said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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 address removed)
MichaelDavid said:
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 said:
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"
Click to expand...
can possibly be

cells(1,23)=0

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
MichaelDavid said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
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 address removed)
MichaelDavid said:
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 said:
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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
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 said:
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 address removed)
MichaelDavid said:
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


:

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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
Range("N2:N" & LstRowData).FormulaR1C1 = _
"=Select Case (RC[-2])"
Click to expand...

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 said:
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 said:
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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
Range("N2:N" & LstRowData).FormulaR1C1 = _
"=Select Case (RC[-2])"
Click to expand...

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 said:
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 said:
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 address 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
Click to expand...
Click to expand...
Click to expand...
 
C

Chuck

By the way, can Excel 2007 handle 14 nested IFs like this?
Click to expand...
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
 
R

Robert McCurdy

--the entire program
currently takes about an hour to complete.
Click to expand...

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 said:
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 said:
Range("N2:N" & LstRowData).FormulaR1C1 = _
"=Select Case (RC[-2])"
Click to expand...

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 said:
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


:

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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
--the entire program
currently takes about an hour to complete.
Click to expand...

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 said:
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 said:
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


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


:

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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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
 
M

MichaelDavid

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=is...&hl=en&sa=X&oi=book_result&resnum=2&ct=result

May you have a most blessed day!

Sincerely,

Michael Fitzpatrick
 
M

MichaelDavid

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 said:
--the entire program
currently takes about an hour to complete.
Click to expand...

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 said:
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 said:
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


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


:

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 address 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
Click to expand...
Click to expand...
Click to expand...
 
M

MichaelDavid

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 said:
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 said:
--the entire program
currently takes about an hour to complete.
Click to expand...

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 said:
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


:

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


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


:

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 address 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Geometric progression in VBA 10
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Auto-calculate after a cell is filled in - Macro is looping!!! 2
Auto-calculate after a cell is filled in - macro shouldn't be loop 5
How do I make this code shorter? Its Result should be given to var 15
Code stops after column "L" 1
workbook open in looping 2
Advanced Filter works in recorder but not while running macro 5

Top