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

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

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

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

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

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

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

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

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