Trouble with swimming time grading in excel

[ohadyemini]

Hi guys, I have a problem and i wish someone here can help me, i searched the web and this site but i didn't found an answer.

I have a table with swimming time for each participant.
The grade is base on a ladder-scale.
If your score is between 15:00 to 16:30 you get 80
If your score is between 16:31 to 17:30 you get 85
you get the point..

i want to calculate the grades for ever participant.
how do i do that using vba?

The main trouble i have is how to represent the scale in vba
and how do i find out if the time given is between a range?

Notes:
I am using excel 2007
The Cells are in the format of 30:55.2

Please Help Me!

[westconn1]

you can try like

For Each cel In Range("a:a")
If IsEmpty(cel) Then Exit For ' finish on empty cell
Select Case cel
Case Is > Range("k1"): cel.Offset(, 1) = 85
Case Is > Range("k2"): cel.Offset(, 1) = 80
Case Is > Range("k3"): cel.Offset(, 1) = 73
End Select
Next
i put some time values in a range K1:K3 in the same format, else time conversions are not simple



16:30.0


15:00.0


13:00.0

[SamT]

Westconn's idea is a nice simple one.

To make it even simpler to understand and modify, I would put the grades in Column J right next to the times in his K1:K3 cells.



K
L


16:30.0
85


15:00.0

80



13:00.0

73






Select Case cel
Case Is >= Range("k1"): cel.Offset(, 1) = Range("L1")
Case Is >= Range("k2"): cel.Offset(, 1) = Range("L2")
Case Is >= Range("k3"): cel.Offset(, 1) = Range("L3")
End Select

[westconn1]

To make it even simplergood thought

[Bob Phillips]

Why would you use VBA, this should be a formula solution?

Put the lookups in a table like so



K
L


15:00.00
80


16:31.00
85


17:31:00
90



etc., and just use this formula

=LOOKUP(A2,$K$1:$K$5,$L$1:$L$5)