Looking for intersection
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
|