Excel formula to complete missing gaps of IP addresses range

Hi,

I have an excel file which has 1 column, data starts in A2 cell (A1=heading)
lets say there's a range of:
191.192.192.0 - 191.192.195.254 in ascending order

I would like somehow to fill in the missing gaps in the correct position, for example:

191.192.192.1
191.192.192.2
191.192.192.3
<automatically insert 191.192.192.4>
191.192.192.5
191.192.194.50
191.192.194.51
<automatically insert 191.192.194.52>
<automatically insert 191.192.194.53>
191.192.194.54

I would like to get a complete solution / VBA script if possible.

Thanks a bunch.

P.S.
Excel 2010
iNc0gAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
That's a bit more complicated, but try this.

Note that is only goes up to .65 instead of .255 for testing purposes. Change the constant value for production runs.
Option Explicit


Sub FillIn()
    Dim r As Integer
    Dim ip4 As Integer
    Dim strFirstThree As String
    Dim sh As Worksheet
    Dim rng As Range
    
    Const ip4Start = 0
    Const ip4End = 66   'test value. Needs to be 256 if live
    

    r = 1
    ip4 = ip4Start
    strFirstThree = Left(Cells(r, 1).Value, 11)
    Do Until Cells(r, 1).Value = ""
        Do While Left(Cells(r, 1).Value, 11) = strFirstThree
            Do Until Split(Cells(r, 1).Value, ".")(3) = ip4
                Rows(r).Insert xlShiftDown
                Cells(r, 1).Value = strFirstThree & "." & ip4
                ip4 = ip4 + 1
                r = r + 1
            Loop
            Do While Left(Cells(r, 1).Value, 11) = strFirstThree And Split(Cells(r, 1).Value, ".")(3) = ip4
                ip4 = ip4 + 1
                r = r + 1
                If Cells(r, 1).Value = "" Then
                    Exit Do
                End If
            Loop
        Loop
        Do Until ip4 = ip4End
            Rows(r).Insert xlShiftDown
            Cells(r, 1).Value = strFirstThree & "." & ip4
            ip4 = ip4 + 1
            r = r + 1
        Loop
        strFirstThree = Left(Cells(r, 1).Value, 11)
        ip4 = ip4Start
    Loop
    
End Sub

Open in new window

0
 
jppintoCommented:
You could put 191.192.192.1 on cell A2 and 191.192.192.2 on cell A3 and then drag below to autofill the rest of the cells creating a list of all of the IP's...
0
 
ukerandiCommented:
see the attached file click on command button it will work
IpAddress.xls
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ukerandiCommented:
Dim count As Long
Dim newNumber As String
Dim last2() As String
Dim Splitnumber As Long

For count = 1 To 100




If Cells(count, 1) = "" Then
newNumber = Cells(count - 1, 1)
last2() = Split(newNumber, ".")
Splitnumber = last2(3) + 1
Cells(count, 1) = last2(0) + "." + last2(1) + "." + last2(2) + "." + CStr(Splitnumber)
'Cells(count, 2) = last2(3)
End If
Next
0
 
StephenJRCommented:
But you're not filling all the gaps, e.g. between 191.192.192.5 and 191.192.194.50?
0
 
GrahamSkanRetiredCommented:
Try this VBA
Option Explicit

Sub FillIn()
    Dim r As Integer
    Dim ip4 As Integer
    Dim strFirstThree As String
    
    r = 1
    ip4 = Split(Cells(r, 1).Value, ".")(3) + 1
    strFirstThree = Left(Cells(r, 1).Value, 11)
    Do Until Cells(r, 1).Value = ""
        r = r + 1
        If Left(Cells(r, 1).Value, 11) = strFirstThree Then
            If Split(Cells(r, 1).Value, ".")(3) > ip4 Then
                Rows(r).Insert xlShiftDown
                Cells(r, 1).Value = strFirstThree & "." & ip4
            End If
        End If
        If Cells(r, 1).Value = "" Then
            Exit Sub
        End If
        ip4 = Split(Cells(r, 1).Value, ".")(3) + 1
        strFirstThree = Left(Cells(r, 1).Value, 11)
    Loop
End Sub

Open in new window

0
 
ukerandiCommented:
if you run my programme it will work,its filling the all the gaps. untill 100 what he has to do is loop extend until 255
Dim count As Long
Dim newNumber As String
Dim last2() As String
Dim Splitnumber As Long

For count = 1 To 255



If Cells(count, 1) = "" Then
newNumber = Cells(count - 1, 1)
last2() = Split(newNumber, ".")
Splitnumber = last2(3) + 1
Cells(count, 1) = last2(0) + "." + last2(1) + "." + last2(2) + "." + CStr(Splitnumber)
'Cells(count, 2) = last2(3)
End If
Next
0
 
GrahamSkanRetiredCommented:
Oops. I have included the heading row.

Therefore line 8 should be
r = 2
0
 
iNc0gAuthor Commented:
I forgot to mention that on column B there's a description of the specific ip owner, meaning the data on columns a + b should stay on the same rows and keep in sync even when ip addresses fill in the gaps
0
 
GrahamSkanRetiredCommented:
Well my macro inserts rows, so all the columns for the the rows below would move down.

ukerandi's macro assumes that there are spaces for the missing IPs so there is no row insertion.

Either way Column B should still match column A.

Have you tried the macros?
0
 
iNc0gAuthor Commented:
Worked great except I need the macro to complete the range completely, for example:

the range 191.192.194 ends at .242 , i need the macro to complete the rest up to 254 , currently it stopped at .242 because that's the last address in that scope.

191.192.194.242
--missing addresses up to 254--
191.192.195.0
191.192.195.1

Thanks.
0
 
GrahamSkanRetiredCommented:
I've also started on the wrong row again, so line 15 should be

 r = 2
0
 
iNc0gAuthor Commented:
Worked great, thanks again.
0
 
iNc0gAuthor Commented:
worked gr8.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.