Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting IP Address
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? |
#2
|
|||
|
|||
Sorting IP Address
Hi,
Pardon me for my ignorance! Are the 4 parts in an IP address called 'octives'? In principle, you could use a combination of LEFT, RIGHT, and LEN functions to isolate the 3rd and 4th octives into separate columns and then sort them by those two columns. But, I thought that the following approach could be simpler. Let us assume that Column A contains the IP numbers. If there are data in other columns (Column B, .....) as well, insert 4 blank columns after A. Copy Column A and paste it in Column B. Select Column B -- "Data" -- "Text to Columns" -- Select "Delimited" -- "Next" -- Select "Other" and enter a 'period' sign in the box -- "Finish". Now, the 3rd and 4th octives will be in columns D and E. After sorting the entire page by columns D and E, you can get rid of columns B, C, D, and E. Please note that all you need is four contiguous blank columns to do the above, and they can be anywhere in the sheet. However I suggested that you have them adjacent to Column A just for convenience. Regards, B. R. Ramachandran "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? |
#3
|
|||
|
|||
Sorting IP Address
B. R.Ramachandran wrote:
Pardon me for my ignorance! Are the 4 parts in an IP address called 'octives'? No. They are called octets. |
#4
|
|||
|
|||
Sorting IP Address
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? Here is a VBA macro that should sort IP addresses. It requires that the IP addresses be in a column (but it does not check for that. To enter this, <alt<F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use the code, first select the column of IP addresses you wish to sort. Then <alt<F8 opens the Macro dialog box. Select SortIP and RUN. Backup your data first, just in case. ========================= 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("0" & Hex(IP(j)), 2) Next j rg(i, 1) = hex2dec(rg(i, 1)) Next i rg = BubbleSort(rg, 1) For i = 0 To UBound(rg) Selection.Cells(i + 1, 1) = rg(i, 0) Next i End Sub ====================== --ron |
#5
|
|||
|
|||
Sorting IP Address
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? My previous post left out some parts of the macro. This one should be complete: ================================== 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("0" & Hex(IP(j)), 2) Next j rg(i, 1) = "&H" & (rg(i, 1)) 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 |
#6
|
|||
|
|||
Sorting IP Address
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 |
#7
|
|||
|
|||
Sorting IP Address
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 |
#8
|
|||
|
|||
Sorting IP Address
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 |
#9
|
|||
|
|||
Sorting IP Address
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 |
#10
|
|||
|
|||
Sorting IP Address
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting IP Address
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting IP Address
Looks like an old thread may have been reactivated, but for sorting IP numbers
take a look at Sorting TCP/IP Addresses, and the like http://www.mvps.org/dmcritchie/excel/sorttcp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate address column to Five columns | Excel Worksheet Functions | |||
Excel email address hyperlink does not update | Excel Discussion (Misc queries) | |||
Sorting an Address List | Excel Worksheet Functions | |||
excel as address book | Excel Discussion (Misc queries) | |||
How do I stop e-mail address from turning into link in Excel? | Excel Discussion (Misc queries) |