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" wrote:
--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