Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
XL2003, Win xp
Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
=MIN(IF(A1:H5J1,A1:H5))
assuming that the A time is in J1, and which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
Sub SortTime() Dim rng As Range Dim cnt As Long, i As Long Dim j As Long Dim temp As Date Set rng = Range("A1:H5") cnt = rng.Count For i = 1 To cnt - 1 For j = i + 1 To cnt If rng(i) rng(j) Then temp = rng(i) rng(i) = rng(j) rng(j) = temp End If Next Next End Sub -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
1:00 1:05 1:10 1:17
1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Hi Tom, Thanks for the code. When I run it and display temp in range("A10") it shows 12:00 - the first time listed. What I actually want is a list of the URLs starting with the earliest if that is possible. Ta, Martin "Tom Ogilvy" wrote in message ... Sub SortTime() Dim rng As Range Dim cnt As Long, i As Long Dim j As Long Dim temp As Date Set rng = Range("A1:H5") cnt = rng.Count For i = 1 To cnt - 1 For j = i + 1 To cnt If rng(i) rng(j) Then temp = rng(i) rng(i) = rng(j) rng(j) = temp End If Next Next End Sub -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
I have no idea what you mean by URL since we are talking about cells
containing time values. Sub SortTime1() Set rng = Range("a1").CurrentRegion ReDim v(1 To rng.Count) As Date For i = 1 To rng.Count v(i) = Application.Small(rng, i) Next Set rng1 = Range("A1").End(xlToRight)(1, 3) rng1.Resize(rng.Count, 1).Value = _ Application.Transpose(v) End Sub Produced: 1:00:00 AM 1:05:00 AM 1:10:00 AM 1:17:00 AM 1:27:00 AM 1:35:00 AM 1:40:00 AM 1:45:00 AM 1:52:00 AM 2:02:00 AM 2:10:00 AM 2:15:00 AM 2:55:00 AM 12:00:00 PM 12:07:00 PM 12:17:00 PM 12:25:00 PM 12:35:00 PM 12:42:00 PM 12:52:00 PM If those don't match what you had in mind (regards AM/PM), it is really irrelevant. If your times are stored correctly, then the point is it will put them sorted into an array. Those AM/PM values are how your data came out when I pasted it into Excel since times/strings from 0:00 to 12:00 are ambiguous if your don't specify AM or PM. -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... 1:00 1:05 1:10 1:17 1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Hi Tom, Thanks for the code. When I run it and display temp in range("A10") it shows 12:00 - the first time listed. What I actually want is a list of the URLs starting with the earliest if that is possible. Ta, Martin "Tom Ogilvy" wrote in message ... Sub SortTime() Dim rng As Range Dim cnt As Long, i As Long Dim j As Long Dim temp As Date Set rng = Range("A1:H5") cnt = rng.Count For i = 1 To cnt - 1 For j = i + 1 To cnt If rng(i) rng(j) Then temp = rng(i) rng(i) = rng(j) rng(j) = temp End If Next Next End Sub -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
Hi Bob,
Thanks for the help. It works! I don't know how you guys figure this stuff out. What my end goal is to get a list of the URL's attached to the times such as:- 1:00 1:05 1:10 1:17 1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Any help would be great. Ta, Martin "Bob Phillips" wrote in message ... =MIN(IF(A1:H5J1,A1:H5)) assuming that the A time is in J1, and which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
Hi Tom,
Sorry about the confusion but I am trying to sort out times in a web query and each time has an embedded URL in it. The web page that supplies the data only specifies the time if it is AM, which is a problem. But thanks for your help and code. It give me a lot to work with. Ta, Martin "Tom Ogilvy" wrote in message ... I have no idea what you mean by URL since we are talking about cells containing time values. Sub SortTime1() Set rng = Range("a1").CurrentRegion ReDim v(1 To rng.Count) As Date For i = 1 To rng.Count v(i) = Application.Small(rng, i) Next Set rng1 = Range("A1").End(xlToRight)(1, 3) rng1.Resize(rng.Count, 1).Value = _ Application.Transpose(v) End Sub Produced: 1:00:00 AM 1:05:00 AM 1:10:00 AM 1:17:00 AM 1:27:00 AM 1:35:00 AM 1:40:00 AM 1:45:00 AM 1:52:00 AM 2:02:00 AM 2:10:00 AM 2:15:00 AM 2:55:00 AM 12:00:00 PM 12:07:00 PM 12:17:00 PM 12:25:00 PM 12:35:00 PM 12:42:00 PM 12:52:00 PM If those don't match what you had in mind (regards AM/PM), it is really irrelevant. If your times are stored correctly, then the point is it will put them sorted into an array. Those AM/PM values are how your data came out when I pasted it into Excel since times/strings from 0:00 to 12:00 are ambiguous if your don't specify AM or PM. -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... 1:00 1:05 1:10 1:17 1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Hi Tom, Thanks for the code. When I run it and display temp in range("A10") it shows 12:00 - the first time listed. What I actually want is a list of the URLs starting with the earliest if that is possible. Ta, Martin "Tom Ogilvy" wrote in message ... Sub SortTime() Dim rng As Range Dim cnt As Long, i As Long Dim j As Long Dim temp As Date Set rng = Range("A1:H5") cnt = rng.Count For i = 1 To cnt - 1 For j = i + 1 To cnt If rng(i) rng(j) Then temp = rng(i) rng(i) = rng(j) rng(j) = temp End If Next Next End Sub -- Regards, Tom Ogilvy "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
Where do URLs come into it?
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... Hi Bob, Thanks for the help. It works! I don't know how you guys figure this stuff out. What my end goal is to get a list of the URL's attached to the times such as:- 1:00 1:05 1:10 1:17 1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Any help would be great. Ta, Martin "Bob Phillips" wrote in message ... =MIN(IF(A1:H5J1,A1:H5)) assuming that the A time is in J1, and which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next time in a range
The range is actually part of a web query with links to other pages.
I guess they did not get posted with the range. Having thought about your code and others(there are 2 other posts) I see I need to tackle the whole thing from a different angle. Thanks for your help. It has helped to get me on the right track. Ta, Martin "Bob Phillips" wrote in message ... Where do URLs come into it? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... Hi Bob, Thanks for the help. It works! I don't know how you guys figure this stuff out. What my end goal is to get a list of the URL's attached to the times such as:- 1:00 1:05 1:10 1:17 1:27 1:35 1:40 1:45 1:52 2:02 2:10 2:15 2:55 12:00 12:07 12:17 12:25 12:35 12:42 12:52 Any help would be great. Ta, Martin "Bob Phillips" wrote in message ... =MIN(IF(A1:H5J1,A1:H5)) assuming that the A time is in J1, and which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Martin Wheeler" wrote in message ... XL2003, Win xp Is there a way to find the next time in a range, where the times are arranged randomly? So in range A1:H5 the code looks for the time after the nominated time. So:- If A = 10:30AM and the next time in the range is 11:15AM then B=11:15AM End if And then I would make A=11:15AM and look for the next time in the range. And so on until I have my list Any help would be greatly appreciated. Ta, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) |