Need VBA Code for the below scenario

Dinesaaa

New Member
Joined
May 23, 2016
Messages
20
Hey Guys,

Below is the scenario to write VBA code:-

> If Score is > 900
> If Income is > 600000
> If Occupation = "Salaried" and Income > 500000
> If Marital Status = "Single"
> If Own house = "yes"

1, If any one of the above criteria met result should be "Loan Eligible"
2, If any one of the above criteria not met "Not Eligible"


Below is my code where "Not Eligible" is not working:-

Function Loan(Cibilscore As Double, Ownhouse As String, Occupation As String, Marritalstatus As String, Income As Double)
If Cibilscore > 900 Then
If LCase(Ownhouse) = "yes" Then
If LCase(Occupation) = "salaried" And Income > 60000 Then
If LCase(Marritalstatus) = "single" Then
Loan = "Eligible"
End If
End If
End If
Else
Loan = "Not Eligible"
End If
End Function
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
You can set loan to not eligible as default then change it if the criteria are met.

Code:
Function Loan(Cibilscore As Double, Ownhouse As String, Occupation As String, Marritalstatus As String, Income As Double)
Loan = "Not Eligible"
If Cibilscore > 900 Then
    If LCase(Ownhouse) = "yes" Then
        If LCase(Occupation) = "salaried" And Income > 60000 Then
            If LCase(Marritalstatus) = "single" Then
                Loan = "Eligible"
            End If
        End If
    End If
End If
End Function
 

Dinesaaa

New Member
Joined
May 23, 2016
Messages
20
I tried the below code but not working???


Function Loaneligibiltycheck(Cibilscore As Double, Ownhouse As String, Occupation As String, Martialstatus As String, Income As Double)
Loaneligibiltycheck = "Not Eligible"
If Cibilscore >= 900 Then
If LCase(Ownhouse) = "yes" Then
If LCase(Occupation) = "salaried" And Income >= 600000 Then
If LCase(Martialstatus) = "single" Then
Loaneligibiltycheck = "Eligible"
End If
End If
End If
End If


End Function


Any corrections please?
 

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
Your selection criterion is somewhat ambiguous. Assuming you mean the following:
1. Any one or more of the criteria met: "Eligible"
2. None of the criteria met: "Not Eligible"

Then the following code will work:

Code:
Function Loan(Cibilscore As Double, Ownhouse As String, Occupation As String, Marritalstatus As String, Income As Double)
If Cibilscore > 900 Then
    Loan = "Eligible"
ElseIf Income > 600000 Then
    Loan = "Eligible"
ElseIf LCase(Ownhouse) = "yes" Then
    Loan = "Eligible"
ElseIf LCase(Occupation) = "salaried" And Income > 500000 Then
    Loan = "Eligible"
ElseIf LCase(Marritalstatus) = "single" Then
    Loan = "Eligible"
Else
    Loan = "Not Eligible"
End If
End Function
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
As an alternative

Code:
Function LoanE(Cibilscore As Double, Ownhouse As String, Occupation As String, Marritalstatus As String, Income As Double)
Dim test As Integer
test = (Cibilscore > 900) * (Income > 600000) * (LCase(Ownhouse) = "yes") * ((LCase(Occupation) = "salaried") + (Income > 500000)) * (LCase(Marritalstatus) = "single")
LoanE = IIf(test = True, "Eligible", "Not Eligible")
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top