Sorting IP Address
There's been nothing in this thread, that I can find, for the past month or so.
Nor do I find any old messages with your handle.
So I don't know what you mean when you say "neither works".
Perhaps if you restate your problem in its entirety, we can help.
On Mon, 28 Nov 2005 07:36:11 -0800, "adetorry"
wrote:
neither works and i dont think if they did it would help my situation
i have list of ip addresses with asscoiated names & tel numbers
if i sort using just one column (ip addys) then the names and tel numbers
with then not be sorted as well ?
ade
"Ron Rosenfeld" wrote:
1. I assume the line where the macro stops is all on one line and not split as
you show it. i.e.
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
2. An invalid IP address (i.e. one without 3 '.''s will cause that error. Is
that a possibility?
For debugging purposes, modify the macro as follows:
....
Replace:
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
Next j
-------------------
with:
If UBound(IP) < 3 Then
MsgBox ("Invalid IP address of " & rg(i, 0) & " at " _
& Selection.Cells(i + 1, 1).Address)
Else
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
Next j
End If
-------------------------------
and let's see what happens.
On Fri, 21 Oct 2005 07:19:02 -0700, "ggant"
wrote:
Still getting error:
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()
ReDim rg(0 To Selection.Count - 1, 0 To 1)
For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
**This where the macro stops<** rg(i, 1) = rg(i, 1) &
Right("000" & IP(j), 3)
Next j
Next i
rg = BubbleSort(rg, 1)
For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
Next i
End Sub
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort
on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean
k = UBound(TempArray, 2)
ReDim Temp(0 To 0, 0 To k)
Do
NoExchanges = True
For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges
BubbleSort = TempArray
End Function
"Ron Rosenfeld" wrote:
Do you have an:
Option Base 1
statement that might be affecting things? (Option Base 0 is the default)
If so, and if you can't remove it, or set Option Base 0 for this module, you'll
have to explicitly declare the lower bounds of all arrays.
============================
In the first SUB, about Line 5
Change:
ReDim rg(Selection.Count - 1, 1)
To:
ReDim rg(0 To Selection.Count - 1, 0 To 1)
In the second SUB, about Line 6
Change:
ReDim Temp(0, k)
To:
ReDim Temp(0 To 0, 0 To k)
========================
On Thu, 20 Oct 2005 07:14:05 -0700, "ggant"
wrote:
Thanks for the help. I am having a problem with an error when I run it.
Runtine error '9'
Subscript out of Range
I think the problem is the last line of the For loop prior to Next j
---------------------------------------------------------------------------
"Ron Rosenfeld" wrote:
On Wed, 19 Oct 2005 07:00:07 -0700, "ggant"
wrote:
How do I sort fields that contain IP numbers in order? ex. Field A -
192.168.1.0.......192.168.1.121.....192.168.1.15 ....192.168.2.12....192.168.2.20...192.168.3.50
How can I sort these by the 3rd and 4th octives?
Slightly simpler:
==============================
==================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()
ReDim rg(Selection.Count - 1, 1)
For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
Next j
Next i
rg = BubbleSort(rg, 1)
For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
Next i
End Sub
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean
k = UBound(TempArray, 2)
ReDim Temp(0, k)
Do
NoExchanges = True
For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges
BubbleSort = TempArray
End Function
==============================
--ron
--ron
--ron
--ron
|