Hello,
I have an excel spreadsheet with 7 colums,(State,LTV,Lien,DTI,FICO LB
and Grade).The first Five columns are given, and I need to determine
the value of Column 6( grade) This can take the values Prime, ALT or
AA.


State LTV Lien DTI FICO Loan Balance Grade

KY 100 1 33.02 653 147388.25 ALT
AZ 100 1 40.78 666 131938.73
IL 93.33 1 29.13 660 164859.52
MO 100 2 42.54 601 138830.91
NC 90 1 29.69 506 93950.63
MO 95 1 37.49 616 151658.03
FL 99.39 1 38.41 719 15 9135.24
NM 89.6 1 41 577 178078.42
VA 90 2 38.42 604 150441.51
IN 100 2 42 600 226520.34
WA 94.81 2 25.75 615 143956.19
MO 100 1 40 759 186061.59
CA 95 1 47.58 608 143255.75
MO 89.31 1 34.24 574 114620.97
HA 100 1 29.09 780 128953.62
NY 75 1 46 592 24658.43
GA 60 1 40 544 24619


I need to embede a button on the side of the table, and the code behind
the button will allow me to grade the loans.

Business rules:

If FICO 700 and Lien=1 and DTI <45 Then Grade = Prime
if Fico 600 and Lien = 1 Then Grade= Alt
If Fico = 525 and Lien =1 or 2 Then Grade = AA
If FICO < 525 Grade is fail

NB
I will need a loop too coz I never know how many rows I will receive


Thanks


--
oscarooko
------------------------------------------------------------------------
oscarooko's Profile: http://www.excelforum.com/member.php...o&userid=28116
View this thread: http://www.excelforum.com/showthread...hreadid=476248

Sub Btn_Click()
Dim lastrow as Long
Dim i as Long
Dim Grade as Range, Fico, Lien, DTI
set lastrow = Cells(rows.count,1).End(xlup).row
for i = 2 to lastrow
Fico = cells(i,5)
Lien = cells(i,3)
DTI = cells(i,4)
set Grade = Cells(i,7)
If FICO 700 and Lien=1 and DTI <45 Then
Grade = "Prime"
elseif Fico 600 and Lien = 1 Then
Grade= "Alt"
elseIf Fico = 525 and (Lien =1 or Lien = 22) Then
Grade = "AA"
elseIf FICO < 525 Then
Grade = "fail"
End if
Loop
End sub

--
Regards,
Tom Ogilvy
"oscarooko" wrote
in message ...

Hello,
I have an excel spreadsheet with 7 colums,(State,LTV,Lien,DTI,FICO LB
and Grade).The first Five columns are given, and I need to determine
the value of Column 6( grade) This can take the values Prime, ALT or
AA.


State LTV Lien DTI FICO Loan Balance Grade

KY 100 1 33.02 653 147388.25 ALT
AZ 100 1 40.78 666 131938.73
IL 93.33 1 29.13 660 164859.52
MO 100 2 42.54 601 138830.91
NC 90 1 29.69 506 93950.63
MO 95 1 37.49 616 151658.03
FL 99.39 1 38.41 719 15 9135.24
NM 89.6 1 41 577 178078.42
VA 90 2 38.42 604 150441.51
IN 100 2 42 600 226520.34
WA 94.81 2 25.75 615 143956.19
MO 100 1 40 759 186061.59
CA 95 1 47.58 608 143255.75
MO 89.31 1 34.24 574 114620.97
HA 100 1 29.09 780 128953.62
NY 75 1 46 592 24658.43
GA 60 1 40 544 24619


I need to embede a button on the side of the table, and the code behind
the button will allow me to grade the loans.

Business rules:

If FICO 700 and Lien=1 and DTI <45 Then Grade = Prime
if Fico 600 and Lien = 1 Then Grade= Alt
If Fico = 525 and Lien =1 or 2 Then Grade = AA
If FICO < 525 Grade is fail

NB
I will need a loop too coz I never know how many rows I will receive


Thanks


--
oscarooko
------------------------------------------------------------------------
oscarooko's Profile:
http://www.excelforum.com/member.php...o&userid=28116
View this thread: http://www.excelforum.com/showthread...hreadid=476248

Thanks Tom, but there was still a little problem. It still gave me an
error. Guess there is something missing thats related to the "set
lastrow...." line

Hope you can help me get a way round it.


--
oscarooko
------------------------------------------------------------------------
oscarooko's Profile: http://www.excelforum.com/member.php...o&userid=28116
View this thread: http://www.excelforum.com/showthread...hreadid=476248

Omit the Set keyword in that line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"oscarooko"
wrote in
message
...

Thanks Tom, but there was still a little problem. It still gave
me an
error. Guess there is something missing thats related to the
"set
lastrow...." line

Hope you can help me get a way round it.


--
oscarooko
------------------------------------------------------------------------
oscarooko's Profile:
http://www.excelforum.com/member.php...o&userid=28116
View this thread:
http://www.excelforum.com/showthread...hreadid=476248

Thanks alot. Peace be with you

--
oscarook
-----------------------------------------------------------------------
oscarooko's Profile: http://www.excelforum.com/member.php...fo&userid=2811
View this thread: http://www.excelforum.com/showthread.php?threadid=47624