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
=MIN(IF(A1:H5>J1,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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
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" <[email protected]> wrote in message
news:eNru%[email protected]...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:eNru%[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >>
> >
> >
>
>
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" <[email protected]> wrote in message
news:%[email protected]...
> =MIN(IF(A1:H5>J1,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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>
>
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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:eNru%[email protected]...
>> >
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>
Where do URLs come into it?
--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
"Martin Wheeler" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > =MIN(IF(A1:H5>J1,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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >>
> >
> >
>
>
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" <[email protected]> wrote in message
news:%[email protected]...
> Where do URLs come into it?
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Martin Wheeler" <[email protected]> wrote in message
> news:#[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > =MIN(IF(A1:H5>J1,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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks