Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for intersection
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for intersection
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for intersection
Ok, thanks.
I gave you the answer with one line of code? Not impressive enough? <g -- Regards, Tom Ogilvy "David" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for intersection
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for intersection
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intersection | Charts and Charting in Excel | |||
Intersection | Excel Discussion (Misc queries) | |||
Intersection help | Excel Discussion (Misc queries) | |||
Intersection | Excel Discussion (Misc queries) | |||
Intersection? | Excel Programming |