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

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