I agree. Suggest you get away from r1c1 and have a look at VBE help for
SELECT CASE
Also,
Application.Goto Reference:="R1C23:R1C23"
ActiveCell.FormulaR1C1 = "0.0"
can possibly be

cells(1,23)=0

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MichaelDavid" wrote in message
...
Greetings! Does anyone know the best way of understanding extremely
complicated Nested IFs in formulas in VBA? Consider the following code
which
I believe I completely debugged over a year ago:

Application.Goto Reference:="R1C23:R1C23"
ActiveCell.FormulaR1C1 = "0.0"
Range("W2:W" & LstRowData).FormulaR1C1 = _
"=IF(RC[-20]=""XIssuer name: "",0.0, " & _
"IF(AND(RC[-12]<""10 - Acquisition or disposition in the
public market "", RC[-12]<""11 - Acquisition or disposition carried out
privately "", RC[-12]<""30 - Acquisition or disposition under a
purchase/ownership plan ""),R[-1]C, " & _
"IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _

"IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0
+
RC[-11]/RC[-8]), " & _

"IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0
+ RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership
:""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), "
& _
"IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership
:""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] -
RC[-11])),
" & _
"IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])), " & _

"IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])), " & _
"IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _

"IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0
+ RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership
:""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] -
RC[-11])), "
& _

"IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))"

I have found this code extremely difficult to understand and modify. Every
time I want to make a small change in one of the deeper nested IFs, I can
count on it taking over 30 minutes. Is there a better way of expressing
the
above code? A better and easier to understand way of accomplishing the
function of the above code? I am open to any and all suggestions. Thanks
in
advance for your help.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick