VB code in Excel

[oscarooko]

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

 

[Tom Ogilvy]

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

 

[oscarooko]

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.

 

[Chip Pearson]

Omit the Set keyword in that line of code.


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


"oscarooko"
message
news[email protected]...

 

[oscarooko]

Thanks alot. Peace be with you