Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel formula to complete missing gaps of IP addresses range

Posted on 2011-09-26
Medium Priority
812 Views
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
0
Question by:iNc0g
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3
• +2

LVL 33

Expert Comment

ID: 36598202
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

LVL 10

Expert Comment

ID: 36598209
see the attached file click on command button it will work
0

LVL 10

Expert Comment

ID: 36598215
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

LVL 24

Expert Comment

ID: 36598228
But you're not filling all the gaps, e.g. between 191.192.192.5 and 191.192.194.50?
0

LVL 76

Expert Comment

ID: 36598263
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
``````
0

LVL 10

Expert Comment

ID: 36598267
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

LVL 76

Expert Comment

ID: 36598312
Oops. I have included the heading row.

Therefore line 8 should be
r = 2
0

Author Comment

ID: 36816725
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

LVL 76

Expert Comment

ID: 36816905
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

Author Comment

ID: 36908285
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
191.192.195.0
191.192.195.1

Thanks.
0

LVL 76

Accepted Solution

GrahamSkan earned 2000 total points
ID: 36914091
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
``````
0

LVL 76

Expert Comment

ID: 36914098
I've also started on the wrong row again, so line 15 should be

r = 2
0

Author Comment

ID: 36914984
Worked great, thanks again.
0

Author Closing Comment

ID: 36914988
worked gr8.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll