Looking for intersection

I have a worksheet set up to allow a user to enter daily class attendance
for a group of 100 students. User makes selections from Data Validation
in AE3, AF3, and AG3 for student name, class name, and class duration,
then clicks a button to enter time spent in that class by that student on
that day. Routine designed to eliminate all the scrolling and locating
that would otherwise be necessary.

ColA made up of 100 blocks (1 per student) starting with student name
followed by a class list
Dates are in Row2

Current way of doing it all that works fine:

Sub FindClass() 'called from button click
Dim ThisClass As String, Rng As Range
Application.ScreenUpdating = False
'---Locate chosen Student name
Columns("A:A").Find(Range("AE3")).Activate
'---Locate chosen Class name below chosen Student name
ThisClass = Range("AF3").Value
Set Rng = Range(ActiveCell.Address, Range(ActiveCell.Address).End
(xlDown))
Rng.Find(ThisClass).Select
'---Enter chosen Class duration
'---under chosen date for chosen Student and Class
'---Allow toggle if mistake is made
'---Apprise user of Class duration entry status
Set Rng = Selection.Offset(0, Rows(2).Find(Date).Column - 1)
If Rng.Value > 0 Then
Rng = ""
Range("AE4") = "Time Deleted"
Range("AE4").Font.ColorIndex = 3
Else
Rng.Value = Range("AG3").Value
Range("AE4") = "Time Added"
Range("AE4").Font.ColorIndex = 10
End If
'---Go back to choose next Student
Range("AE3").Select
'---Turn off Class duration entry status display
Application.OnTime Now + TimeValue("0:00:02"), "BlankIt"
End Sub

Sub BlankIt()
Range("AE4") = ""
End Sub

What I'm thinking is that instead of this 'linear' way, a shorter routine
could find the intersection of the 3 variables.
I want to impress my friends <g>

--
David

Looks Short enough to me. Even an intersection would require that you know
what row the class is located in.

you could use the below formula in a cell

=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)

So you could code this up in VBA

Sub BBBB()
Dim rng As Range
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)
Debug.Print rng.Address

End Sub

Then use rng to do your work.



--
Regards,
Tom Ogilvy

"David" <[email protected]> wrote in message
news:OW%[email protected]...
> I have a worksheet set up to allow a user to enter daily class attendance
> for a group of 100 students. User makes selections from Data Validation
> in AE3, AF3, and AG3 for student name, class name, and class duration,
> then clicks a button to enter time spent in that class by that student on
> that day. Routine designed to eliminate all the scrolling and locating
> that would otherwise be necessary.
>
> ColA made up of 100 blocks (1 per student) starting with student name
> followed by a class list
> Dates are in Row2
>
> Current way of doing it all that works fine:
>
> Sub FindClass() 'called from button click
> Dim ThisClass As String, Rng As Range
> Application.ScreenUpdating = False
> '---Locate chosen Student name
> Columns("A:A").Find(Range("AE3")).Activate
> '---Locate chosen Class name below chosen Student name
> ThisClass = Range("AF3").Value
> Set Rng = Range(ActiveCell.Address, Range(ActiveCell.Address).End
> (xlDown))
> Rng.Find(ThisClass).Select
> '---Enter chosen Class duration
> '---under chosen date for chosen Student and Class
> '---Allow toggle if mistake is made
> '---Apprise user of Class duration entry status
> Set Rng = Selection.Offset(0, Rows(2).Find(Date).Column - 1)
> If Rng.Value > 0 Then
> Rng = ""
> Range("AE4") = "Time Deleted"
> Range("AE4").Font.ColorIndex = 3
> Else
> Rng.Value = Range("AG3").Value
> Range("AE4") = "Time Added"
> Range("AE4").Font.ColorIndex = 10
> End If
> '---Go back to choose next Student
> Range("AE3").Select
> '---Turn off Class duration entry status display
> Application.OnTime Now + TimeValue("0:00:02"), "BlankIt"
> End Sub
>
> Sub BlankIt()
> Range("AE4") = ""
> End Sub
>
> What I'm thinking is that instead of this 'linear' way, a shorter routine
> could find the intersection of the 3 variables.
> I want to impress my friends <g>
>
> --
> David

Tom Ogilvy wrote

> Looks Short enough to me. Even an intersection would require that you
> know what row the class is located in.

Ok, thanks.

--
David

> Ok, thanks.

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

--
Regards,
Tom Ogilvy


"David" <[email protected]> wrote in message
news:[email protected]...
> Tom Ogilvy wrote
>
> > Looks Short enough to me. Even an intersection would require that you
> > know what row the class is located in.
>
> Ok, thanks.
>
> --
> David

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Tom Ogilvy wrote

> 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.

As long as it stops after the first find of the class name, that's fine. No
way to predict the number of classes that would be available for students
as we change them almost monthly, but it's hard to imagine exceeding 20.

Any downside to having it set higher than needed (with maybe a marginal
buffer for future additions of classes? Could I replace 100 with a
variable, such as CountA(Range("Classes")) that would auto adjust?

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

Understood. Thanks

--
David

My assumption is you have

Student Name1
class101
class207
class132
class121
class275
StudentName2
class101
class502
class442
class121
class275

the only reservation I would have about larger would be if the user put
Student Name 1 and class442 (which student name 1 does not have). If the
number of rows to search is too large, it could find the 442 under Student
Name 2 rather than return an error. However, if set to 20 and the actual
number is 15 for example, I would consider this low risk.

--
Regards,
Tom Ogilvy




"David" <[email protected]> wrote in message
news:[email protected]...
> Tom Ogilvy wrote
>
> > 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.
>
> As long as it stops after the first find of the class name, that's fine.
No
> way to predict the number of classes that would be available for students
> as we change them almost monthly, but it's hard to imagine exceeding 20.
>
> Any downside to having it set higher than needed (with maybe a marginal
> buffer for future additions of classes? Could I replace 100 with a
> variable, such as CountA(Range("Classes")) that would auto adjust?
>
> > It looks at all of column A to find the student, so no limitation
> > there.
>
> Understood. Thanks
>
> --
> David

Tom Ogilvy wrote

> My assumption is you have
>
> Student Name1
> class101
> class207
> class132
> class121
> class275
> StudentName2
> class101
> class502
> class442
> class121
> class275
>
> the only reservation I would have about larger would be if the user
> put Student Name 1 and class442 (which student name 1 does not have).
> If the number of rows to search is too large, it could find the 442
> under Student Name 2 rather than return an error. However, if set
> to 20 and the actual number is 15 for example, I would consider this
> low risk.
>

The class list (currently 17 offered per week) is identical for all
students -- a "weekly menu" if you like.
The layout you describe is accurate, just not that each student's name is
followed by a unique class list.

StudentName1
Classes 1-17
StudentName2
Classes 1-17
etc.

--
David

Tom Ogilvy wrote
<snip>

I have a dynamic range named "Classes" that reflects that list. I'm
thinking I could adjust the line to read:

sStr = "offset(A1,match(AF3,offset($A$1,match(AE3,A:A,0)-1,0,counta
(Classes),1),0)-1" & _
"+match(AE3,A:A,0)-1,Match(Today(),2:2,0)-1,1,1)"

Seems to work in my limited testing.

--
David

David wrote

> Seems to work in my limited testing.

had to adjust to counta(Classes)+1 to get to last class in the list.

--
David

To eliminate the evaluate:

Sub ABCDEF()
Dim cnt as Long, rng as Range
cnt = Application.CountA(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


--
Regards,
Tom Ogilvy

"David" <[email protected]> wrote in message
news:[email protected]...
> David wrote
>
> > Seems to work in my limited testing.
>
> had to adjust to counta(Classes)+1 to get to last class in the list.
>
> --
> David

slight correction assuming classes is a 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

--
Regards,
Tom Ogilvy
"Tom Ogilvy" <[email protected]> wrote in message
news:e%[email protected]...
> To eliminate the evaluate:
>
> Sub ABCDEF()
> Dim cnt as Long, rng as Range
> cnt = Application.CountA(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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > David wrote
> >
> > > Seems to work in my limited testing.
> >
> > had to adjust to counta(Classes)+1 to get to last class in the list.
> >
> > --
> > David
>
>

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 ***** 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

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

--
Regards,
Tom Ogilvy


"David" <[email protected]> wrote in message
news:[email protected]...
> 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 ***** 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