Tom Ogilvy wrote

I gave you the answer with one line of code? Not impressive enough? <g

The 'you could use the below formula in a cell' was not something I want.

And..

<quote
sStr = "offset(A1,match(AF3,offset($A$1,match(AE3,A:A ,0)-1,0,100,1),0)-1" &
_
"+match(AE3,A:A,0)-1,Match(Today(),2:2,0)-1,1,1)"
Set rng = Evaluate(sStr)

Then use rng to do your work.
<end quote

I plugged this in just above "If Rng.Value 0 Then" and it works (I *am*
impressed!!) for current list, but appears to be married to 100 students.
That number changes frequently. Surprized to find it even works with 102
students. What does the 100 refer to? I would hate to forget to change it.

--
David

It isn't married to any number of students. I misunderstood your post to
say each student was a block of 100, but you actually said

ColA made up of 100 blocks (1 per student)

so the 100 could be changed to the maximum size of a block. It only
describes the area to search after the student has been found.

so right now, if the student is found in A1023, it would look at A1023 to
A1122 to find the class name.

It looks at all of column A to find the student, so no limitation there.

--
Regards,
Tom Ogilvy


"David" wrote in message
...
Tom Ogilvy wrote

I gave you the answer with one line of code? Not impressive enough? <g

The 'you could use the below formula in a cell' was not something I want.

And..

<quote
sStr = "offset(A1,match(AF3,offset($A$1,match(AE3,A:A ,0)-1,0,100,1),0)-1"
&
_
"+match(AE3,A:A,0)-1,Match(Today(),2:2,0)-1,1,1)"
Set rng = Evaluate(sStr)

Then use rng to do your work.
<end quote

I plugged this in just above "If Rng.Value 0 Then" and it works (I *am*
impressed!!) for current list, but appears to be married to 100 students.
That number changes frequently. Surprized to find it even works with 102
students. What does the 100 refer to? I would hate to forget to change it.

--
David