Finding closest value within an array

[Stewart]

I've got some results which I need to grade against some
benchmarks e.g.
A=90% and above
B=80% and above
C=70% and above

I've got 10000 pupils with varying results is there any
macro I can create to give each pupil a grade for their
result and put it on a worksheet (I would use formulas but
that makes the workbook way too big - as I have to repeat
this for 18 subjects!)

Thank you for any help you can give.

Stewart

 

[Norman Jones]

Hi Stewart,

I would use formulas but
that makes the workbook way too big - as I have to repeat
this for 18 subjects!

Use your formulas and then copy / paste special / values to convert the
formulas to values.

If you still want a macro solution, please give details of the data layout.

 

[Iain King]

A=90% and above
B=80% and above
C=70% and above

I've got 10000 pupils with varying results is there any
macro I can create to give each pupil a grade for their
result and put it on a worksheet (I would use formulas but

range("A65536").select ' this bit finds the last row which contains
data
activecell.end(xlUp).select
lastrow = activecell.row

with sheets("Sheet1") 'or whatever your sheet is called
for r = 1 to lastrow
mark = .cells( r, 2).value 'looking for mark in
column 2 (i.e. B)
if mark >= 90 then
grade = "A"
elseif mark >= 80 then
grade = "B"
elseif mark >= 70 then
grade = "C"
else
grade = "D"
endif
.cells( r, 3).value = grade 'write value into column
3 (i.e. C)
next
end with

Iain King

 

[Stewart]

The thing is that I want to have static data tables then a
macro to work out grades when called upon, not calculate
them then display them. The reason for this is that these
are provisional results and are likely to be a state of
flux, so if I can just dump the data in then run a macro
that is the better solution.

Is there anyway of creating an array then looking up the
value against the array in a similar way that the match
function works?

e.g. the array would be (90,80,70) the value to look up
would be 76 and you'd want it to return 70 as it is above
70 but less than 80.

Stewart

 

[Stewart]

Easy when you know how - cheers, that's given me an idea
on how to do the other part of the problem I've got too -
nice one.

Stewart

-----Original Message-----
but


range("A65536").select ' this bit finds the last row which contains
data
activecell.end(xlUp).select
lastrow = activecell.row

with sheets("Sheet1") 'or whatever your sheet is called
for r = 1 to lastrow
mark = .cells( r,

2).value 'looking for mark in