--the entire program
currently takes about an hour to complete.
Did you change calculation to Manual?
It shouldn't take more than a few seconds.
You can also use...
Range("N2:N4000").Formula = "=MyFx(A2,C2,E2)"
Without filling down.
No ones asked - outa sheer fear no doubt - but what is the reason for the formula?
Can you not just program the values to the desired cells?
I remember when I tried to use these things when I was experienced with the XL interface, but very green using VBA. I kept using what I knew best with XL formulae, and pulling that into my code constructs.
What a mess I created! To this day I can not figure out what the hell most of the code I wrote then, supposed to do!
Unfortunately, what you posted today will only show to an experienced programmer, that it was written by someone that is not at all familiar with VBA.
Two really good features you can use, is to filter your data or use the Advanced Filter function. And Pivot Tables. (is that 3?)
Advanced filter will take a zillion criteria and zap the results where ever you like. Just edit the recorded code, so the output is on another sheet, as well as the Criteria and Copyto range.
Another way to reduce that 'IF' formula, is to use either Choose or Lookup.
Check them out from the Help files.
Lastly the best advice you can get is:
Give up smoking!
Ok you don't smoke then:
Eat a balanced diet and exercise regularly.
You already do; Well, great advice wasn't it?
Regards
Robert McCurdy
"MichaelDavid" wrote in message ...
Greetings again! I just hope that propagating a function call through the
range doesn't cause execution time to take a serious hit--the entire program
currently takes about an hour to complete.
--
May you have a most blessed day!
Sincerely,
Michael Fitzpatrick
"Dana DeLouis" wrote:
Range("N2:N" & LstRowData).FormulaR1C1 = _
"=Select Case (RC[-2])"
also:
Range("N2:N" & LstRowData).FormulaR1C1 =
Hi. It appears to me that you are putting "Select Case" on the worksheet. It should just be part of your vba formula.
Also, I would suggest that you just work with the formula in N2 at first. When you are finished, just copy it down.
This is a very simple outline. What goes on the worksheet is just the name of your function, along with the input cells.
Hope this simple example helps out.
Sub MyMainRoutine()
'Have your formula point to all the input cells
Range("N2").Formula = "=MyFx(A2,C2,E2)"
'When your formula is correct, copy it down.
Range("N2:N13").FillDown
End Sub
Function MyFx(ColA, ColC, ColE)
Dim Answer
'// Do all your calculations here
'// where they can be documented.
Select Case ColC
Case "F3 Start Month"
Answer = 999
Case Else
Answer = 0
End Select
'//Return solution
MyFx = Answer
End Function
--
Dana DeLouis
"MichaelDavid" wrote in message ...
Greetings Don:
I tried your suggestion, but Excel VBA does not seem to like such
constructions as:
Range("N2:N" & LstRowData).FormulaR1C1 = _
"=Select Case (RC[-2])"
Case Is = "F3 Start Month":
RC [-2] = 999
Case Else
RC [-2] = 0
End Select
Although the VBE allows me to enter the above code, when I try to execute
it, I get the message: "Compile error: Case without Select Case". Please keep
in mind that I am trying to enter formulas throughout the range from N2
through N & LstRowData, where LstRowData can be about Row 4000; i.e. N2:N4000.
--
May you have a most blessed day!
Sincerely,
Michael Fitzpatrick
"Don Guillett" wrote:
I agree. Suggest you get away from r1c1 and have a look at VBE help for
SELECT CASE
Also,
Application.Goto Reference:="R1C23:R1C23"
ActiveCell.FormulaR1C1 = "0.0"
can possibly be
cells(1,23)=0
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"MichaelDavid" wrote in message
...
Greetings! Does anyone know the best way of understanding extremely
complicated Nested IFs in formulas in VBA? Consider the following code
which
I believe I completely debugged over a year ago:
Application.Goto Reference:="R1C23:R1C23"
ActiveCell.FormulaR1C1 = "0.0"
Range("W2:W" & LstRowData).FormulaR1C1 = _
"=IF(RC[-20]=""XIssuer name: "",0.0, " & _
"IF(AND(RC[-12]<""10 - Acquisition or disposition in the
public market "", RC[-12]<""11 - Acquisition or disposition carried out
privately "", RC[-12]<""30 - Acquisition or disposition under a
purchase/ownership plan ""),R[-1]C, " & _
"IF(AND(RC[3]=""SR"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""xc"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""SR"",RC[-11]0.0),R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0
+
RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""xc"",RC[-11]0.0),R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0
+ RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""SR"",RC[-13]=""Direct Ownership
:""),R[-1]C+RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] - RC[-11])), "
& _
"IF(AND(RC[3]=""xc"",RC[-13]=""Direct Ownership
:""),R[-1]C+R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] -
RC[-11])),
" & _
"IF(RC[3]=""SR"",R[-1]C+R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])), " & _
"IF(RC[3]=""xc"",R[-1]C+R2C12*R2C10*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])), " & _
"IF(AND(RC[3]=""DIR"",RC[-11]0.0,RC[-13]=""Direct Ownership
:""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 + RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""DIR"",RC[-11]0.0),R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0
+ RC[-11]/RC[-8]), " & _
"IF(AND(RC[3]=""DIR"",RC[-13]=""Direct Ownership
:""),R[-1]C+0.5*RC[-16]*RC[-11]*RC[-10]*(1.0 - RC[-11]/(RC[-8] -
RC[-11])), "
& _
"IF(RC[3]=""DIR"",R[-1]C+0.5*R2C12*RC[-16]*RC[-11]*RC[-10]*(1.0 -
RC[-11]/(RC[-8] - RC[-11])),R[-1]C))))))))))))))"
I have found this code extremely difficult to understand and modify. Every
time I want to make a small change in one of the deeper nested IFs, I can
count on it taking over 30 minutes. Is there a better way of expressing
the
above code? A better and easier to understand way of accomplishing the
function of the above code? I am open to any and all suggestions. Thanks
in
advance for your help.
--
May you have a most blessed day!
Sincerely,
Michael Fitzpatrick