#NAME? error in nested if & VB

[Rod]

I have many cellse (5 x 10) usine up to 6 nested ifs in each cell. a cell
looks similar to:

=IF(AND(ISNUMBER(Summary_GOOD),AB40),CompDM($D40,"GOOD",Summary_GOOD)-IF(AB39,CompDM($D39,"GOOD",Summary_GOOD),IF(AB38,CompDM($D38,"GOOD",Summary_GOOD),IF(AB37,CompDM($D37,"GOOD",Summary_GOOD),IF(AB36,CompDM($D36,"GOOD",Summary_GOOD),IF(Summary_GOOD_Writer="REP",CompDM("REP","GOOD",Summary_GOOD),0))))))

In an attempt to reduce the size of the formula I created in VB (1st attempt
at VB:

Function CompDM(Contract_Level As String, Product As String, Loan_Amount As
Single) As Single
Dim ContractPercent As Single

ComDM = 0
Select Case Product
Case "SMART", "Smart", "smart"
Select Case Contract_Level
Case "Rep", "REP", "rep"
ContractPercent = 0.0031
Case "SRep", "SREP", "srep", "Srep"
ContractPercent = 0.0036
Case "Dis", "DIS", "dis"
ContractPercent = 0.0044
Case "Div", "DIV", "div"
ContractPercent = 0.0057
Case "Reg", "REG", "reg"
ContractPercent = 0.0083
Case "SReg", "SREG", "sreg", "Sreg"
ContractPercent = 0.0083
Case "RVP", "rvp", "Rvp"
ContractPercent = 0.0123
End Select
Case "GOOD", "good", "Good"
Select Case Contract_Level
Case "Rep", "REP", "rep"
ContractPercent = 0.0031
Case "SRep", "SREP", "srep", "Srep"
ContractPercent = 0.0036
Case "Dis", "DIS", "dis"
ContractPercent = 0.0044
Case "Div", "DIV", "div"
ContractPercent = 0.0057
Case "Reg", "REG", "reg"
ContractPercent = 0.0083
Case "SReg", "SREG", "sreg", "Sreg"
ContractPercent = 0.0083
Case "RVP", "rvp", "Rvp"
ContractPercent = 0.0125
End Select
End Select
CompDM = Loan_Amount * ContractPercent
End Function

Cell Values:
D36 through D40:
SREP
DIS
DIV
REG
RVP

Summary_Good = 100000
Summary_GOOD_Writer = "DIV"

AB38 = FALSE
AB39 = FALSE
AB40 = FALSE
AB41 = FALSE
AB41 = TRUE
AB42 = TRUE

The value for the last If statement comes back
Logical_test: "= FALSE"
Value_if_true: "= 310"
Value_if_false: "= 0"
Result of function: "=0"
The value for the second to the last if comes back:
Logical_test: "= FALSE"
Value_if_true: "= 360"
Value_if_false: "=0
Result of function: "=" (there is nothing shown)
The value for the third to the last if comes back:
Logical_test = FALSE
Value_if_true "= 440"
Value_if_false "= #NAME?"
Result for the function " = " (nothing is shown)

How can I resolve this? The #NAME? continues in the Value_if_false and the
function results are blank in the 4th & 5th to the last statments. the very
first if statment:
Logical_test: "= TRUE"
Value_if_tru: "= #NAME?"
Value_if_false: "= any" (grayed out)
Result of function: "=" (nothing shows)

However, the cell holding this forluma (K40) results in 420. So, it looks
like part of the problem is the resulting "NAME?" but the root problem is
what is causing this and how can I fix it?

Thx VERY MUCh for your help!!!

 

[Ryan H]

I couldn't really follow your post really well, so I took the liberty to
clean up your function syntax a bit, which may help identify your issue. I
think this part of your function is not working because ContractPercent is
not getting a value.

CompDM = Loan_Amount * ContractPercent

Try this function and tell me if you are still having issues. I put some
message boxes in the function to indicate the possible issue. Hope this
helps! If so, let me know, click "YES" below.


Function CompDM(Contract_Level As String, Product As String, Loan_Amount As
Single) As Single

Dim ContractPercent As Single

ComDM = 0
Select Case UCase(Product)
Case "SMART"
Select Case UCase(Contract_Level)
Case "REP": ContractPercent = 0.0031
Case "SREP": ContractPercent = 0.0036
Case "DIS": ContractPercent = 0.0044
Case "DIV": ContractPercent = 0.0057
Case "REG": ContractPercent = 0.0083
Case "SREG": ContractPercent = 0.0083
Case "RVP": ContractPercent = 0.0123
Case Else: MsgBox "No Contract_Level for Smart Product"
End Select
Case "GOOD"
Select Case UCase(Contract_Level)
Case "REP": ContractPercent = 0.0031
Case "SREP": ContractPercent = 0.0036
Case "DIS": ContractPercent = 0.0044
Case "DIV": ContractPercent = 0.0057
Case "REG": ContractPercent = 0.0083
Case "SREG": ContractPercent = 0.0083
Case "RVP": ContractPercent = 0.0125
Case Else: MsgBox "No Contract_Level for Good Product"
End Select
Case Else: MsgBox "No Product"
End Select
CompDM = Loan_Amount * ContractPercent

End Function

 

[joel]

I would do the entire formula in a VBA function and pass all th
reference cells in the parameter list. Then you can put a break poin
into the function and step through the code to find all your problems.
You can use in VBA ISNUMERIC() to test if the input is a number.

=CompDM(........

 

[p45cal]

First regarding the isname errors:
Assuming Summary_Good and Summary_GOOD_Writer are both single-cell
named ranges, you'll see that these errors disappear if you use the
actual cell references throughout the formula instead of the name.
It seems to give the same result so I wouldn't worry about it.

Second regarding the formula:
A part of it seems to effectively run up the values from AB39 to AB36
looking for the first TRUE and acting on it, disregarding the values
above the first TRUE value. This is ideally suited for the Select Case
construct in vba, but there are also other, shorter, ways of doing that
within formulas. I'd like to know what makes these values True/False in
order to possibly cut out the middle man.

What is it you're trying to do?

Third, the vba:
It seems to be split into broadly two parts, one for Product = Smart,
the other for Product = Good, and the only difference I can see is a
small difference in the value assigned to ContractPercent when
Contract_Level = RVP; in one case .0125, in the other .0123 (is this a
typo?). So we should be able to shorten the vba a bit which will make it
easier to tweak in the future by only using each value one place. I also
see you're using the likes of "SMART", "Smart", "smart", to account for
the various ways people might enter that word.. there's a shorter way
and it includes more variations., Your current vba could look like:

Code:
--------------------
Function CompDM(Contract_Level As String, Product As String, Loan_Amount As Single) As Single
Dim ContractPercent As Single
Select Case UCase(Product)
Case "SMART", "GOOD"
Select Case UCase(Contract_Level)
Case "REP"
ContractPercent = 0.0031
Case "SREP"
ContractPercent = 0.0036
Case "DIS"
ContractPercent = 0.0044
Case "DIV"
ContractPercent = 0.0057
Case "REG"
ContractPercent = 0.0083
Case "SREG"
ContractPercent = 0.0083
Case "RVP"
ContractPercent = IIf(UCase(Product) = "SMART", 0.0123, 0.0125)
End Select
End Select
CompDM = Loan_Amount * ContractPercent
End Function

--------------------
and a bit shorter if Product can only be either GOOD or SMART and
nothing else:

Code:
--------------------
Function CompDM(Contract_Level As String, Product As String, Loan_Amount As Single) As Single
Dim ContractPercent As Single
Select Case UCase(Contract_Level)
Case "REP"
ContractPercent = 0.0031
Case "SREP"
ContractPercent = 0.0036
Case "DIS"
ContractPercent = 0.0044
Case "DIV"
ContractPercent = 0.0057
Case "REG", "SREG"
ContractPercent = 0.0083
Case "RVP"
ContractPercent = IIf(UCase(Product) = "SMART", 0.0123, 0.0125)
End Select
CompDM = Loan_Amount * ContractPercent
End Function

--------------------

Having said that, I agree with joel in that the formulae on the sheet,
however short we could make them, would still be quite difficult to
understand so it would warrant the whole lot being put into one user
defined function - but we'd need to know more about what you're trying
to do to be able to make it as straightforward and robust as possible.

(I haven't tested either of the functions above.)