I confused a terse email from another David about a different situation as
being from you. You can disregard.

--
Regards,
Tom Ogilvy


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

slight correction assuming classes is a named range:

As stated earlier, it is a dynamic named range

To eliminate the evaluate:

Sub ABCDEF()
Dim cnt as Long, rng as Range
cnt = Application.CountA(Range("Classes")) + 1
With ActiveSheet
Set rng = .Range("A1").Offset( _
Application.Match(.Range("AF3").Value, .Range("A1").Offset( _
Application.Match(.Range("AE3"), .Range("A:A"), 0) - 1, 0) _
.Resize(cnt, 1), 0) - 1 +
.Range("A1").Offset(Application.Match( _ .Range("AE3"),
.Range("A:A"), 0) - 1, 0).Row - 1, _
Application.Match(CLng(Date), .Rows(2), 0) - 1)

End With
MsgBox rng.Address
End Sub

Yup, that works, too. Any advantage (I'm trying to learn), or just
offering alternative?

Current code (modified to match sub name and MsgBox) does same and at
least to the naked eye with less overhead:

Sub ABCDEF()
Dim Rng As Range
Application.ScreenUpdating = False
sStr = "offset(A1,match(AF3,offset(" & _
"$A$1,match(AE3,A:A,0)-1,0,counta(Classes)+1,1),0)-1" & _
"+match(AE3,A:A,0)-1,Match(Today(),2:2,0)-1,1,1)"
Set Rng = Evaluate(sStr)
MsgBox Rng.Address
End Sub

--
David