#NAME? error in nested if & VB

  • Thread starter Thread starter Rod
  • Start date Start date
R

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!!!
 
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
 
Back
Top