RobOttman
New Member
- Joined
- Feb 15, 2017
- Messages
- 1
I have been searching all day for answers and have made zero progress. I hope that you can help me!
I have one worksheet ("Schedule") and in column L there is the StartTime for all of our classes. On another worksheet("Times") I have a list of the standard start times for class periods. What I want to do is have a VBA macro start at the top of column L in Schedules, then find the row number (or, if it's easier, return a value from a neighboring column, which would just be the row number anyway) for the time time range where the "active cell" of column L falls. I would then have VBA put this value in column M.
So "Times" looks like this:
<colgroup><col></colgroup><tbody>
</tbody>
Column L of "Schedule" contains times like 8:15 AM and 9:20 AM so that when I run the macro, column M would display "1" and "2" respectively (only 1 time per row! I'm not trying to get two periods out of one start time!). Similarly, if the start time is, say, 5:20 PM, the macro would return "9", since "5:20 PM" is between 4:00 PM and 7:00 PM.
I have tried using function, such as Match and VLookup, but those have not worked. I can't figure out how to use variables in VLookup, and for some reason Excel is unable to "MATCH" the times as exported from Access. (That is another problem entirely! If I export the query, the two times appear 100% identical, but will never match. If I copy the column from Access and paste it into Excel, they'll match as you would expect. I have no idea why...)
So any light you could shed on how to compare the value of one cell to a range of values in another worksheet via VBA would be greatly appreciated!
I have one worksheet ("Schedule") and in column L there is the StartTime for all of our classes. On another worksheet("Times") I have a list of the standard start times for class periods. What I want to do is have a VBA macro start at the top of column L in Schedules, then find the row number (or, if it's easier, return a value from a neighboring column, which would just be the row number anyway) for the time time range where the "active cell" of column L falls. I would then have VBA put this value in column M.
So "Times" looks like this:
| 8:15 AM |
| 9:20 AM |
| 10:25 AM |
| 11:30 AM |
| 12:35 PM |
| 1:40 PM |
| 2:45 PM |
| 3:50 PM |
| 4:00 PM |
| 7:00 PM |
<colgroup><col></colgroup><tbody>
</tbody>
Column L of "Schedule" contains times like 8:15 AM and 9:20 AM so that when I run the macro, column M would display "1" and "2" respectively (only 1 time per row! I'm not trying to get two periods out of one start time!). Similarly, if the start time is, say, 5:20 PM, the macro would return "9", since "5:20 PM" is between 4:00 PM and 7:00 PM.
I have tried using function, such as Match and VLookup, but those have not worked. I can't figure out how to use variables in VLookup, and for some reason Excel is unable to "MATCH" the times as exported from Access. (That is another problem entirely! If I export the query, the two times appear 100% identical, but will never match. If I copy the column from Access and paste it into Excel, they'll match as you would expect. I have no idea why...)
So any light you could shed on how to compare the value of one cell to a range of values in another worksheet via VBA would be greatly appreciated!
