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

--
Regards,
Tom Ogilvy
"oscarooko" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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
>

[oscarooko]

Thanks alot. Peace be with you.