#### rs2k

##### Well-known Member
I am trying to build a spreadsheet that allocates IP addresses and VLANs. We are given an IP range per site and from that we have to allocate the relative VLAN and IP address to suit. We have several hundred (possibly a thousand) sites which have users going into the thousands. To make my life easier I have been trying to come up with something that I can enter the start and end of the allocated range and it autofills to give everything an IP address.

I currently have userforms that transfers data to a WS.
Book1
ABCDEF
21GX2
37GX3
42GX2
531X1
611X2
75GX1
82GX2
Sheet1

I want the data to end up like,
Book1
ABCDEF
10Start IPEnd IP
1110.1.1.110.1.1.254
12
147GX310.1.1.1
151GX210.1.1.2
1611X210.1.1.3
172GX210.1.1.4
182GX210.1.1.5
1931X110.1.1.6
205GX110.1.1.7
Sheet1

There are other columns involved but these can be worked around, the columns in the example are what are important.
Since the original data will be entered randomly, I need to sort by the highest number of TAPs on any one floor, so a building with two floors may have the most TAPs combined, but not compared to another building with more TAPs on one floor (Is this making sense?)

When the Start IP and End IP are entered into their cells, the formula/code will autofill down and give an IP address to every TAP.

Any comments or advice on how to do this would be greatfully recieved.

Many thanks
Colin.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi colin, in true 'Blue Peter' tradition, here's something I prepared earlier:
Code:
``````Option Explicit
Public pRow As Long
Public pWS As Worksheet
Sub IPCombos()
'Sheet1 Cell a1 = start IP address (format 111.222.333.444.555)
'Sheet1 Cell A2 = End IP address (format 111.222.333.444.555)
'Sheet2 column A is cleared & will then be populated with the ip addresses
Dim WS As Worksheet

Set WS = Sheets("Sheet1")
pRow = 0
Set pWS = Sheets("Sheet2")
pWS.Columns("A").ClearContents
IPNext WS.Range("A1").Text, WS.Range("A2").Text, 0
End Sub
Sub IPNext(ByVal IPStart As String, _
ByVal IPEnd As String, _
ByVal Index As Integer)
Dim iMax As Integer, iPtr As Integer, iLen As Integer
Dim iCount As Integer, iStart As Integer, iEnd As Integer
Dim vStart As Variant, vEnd As Variant

vStart = Split(IPStart, ".")
vEnd = Split(IPEnd, ".")

iMax = UBound(vEnd)
If iMax > UBound(vStart) Then iMax = UBound(vStart)
iStart = Val(vStart(Index))
iEnd = Val(vEnd(Index))
If iEnd < iStart Then iEnd = iStart
For iCount = iStart To iEnd
vStart(Index) = iCount
IPStart = Join(vStart, ".")
If WorksheetFunction.CountIf(pWS.Columns("A"), IPStart) = 0 Then
pRow = pRow + 1
pWS.Cells(pRow, "A").Value = IPStart
End If
For iPtr = Index + 1 To iMax
IPNext IPStart:=IPStart, IPEnd:=IPEnd, Index:=iPtr
Next iPtr
Next iCount
End Sub``````

Imsert into it's own module, amend IPCombos macro which currently expects the start IP address in Sheet1 cell A1 & the end IP address in Sheet1, cell A2, and outputs the series in Sheet2 column A

Alan,
I think Im doing something wrong. I have reset my work sheet to align with your code;
'start IP address in Sheet1 cell A1 & the end IP address in Sheet1, cell A2, and outputs the series in Sheet2 column A'

But, nothing seems to happen?
I opened VBA editor chose Insert|module and pasted the code in there, is that correct?

Colin.

Alan,
User error, the muppet at my end got it wrong.

Many thanks, this has saved me many hours of laborious work.

Cheers
Colin.

Hi colin, looks good to me

Just re-tested on a new sheet, with this input:
Book1
ABCD
11.1.1.1.1
21.1.1.15.1
Sheet1

which, after running IPCOMBOS gives this result:
Book1
ABCD
11.1.1.1.1
21.1.1.2.1
31.1.1.3.1
41.1.1.4.1
51.1.1.5.1
61.1.1.6.1
71.1.1.7.1
81.1.1.8.1
91.1.1.9.1
101.1.1.10.1
111.1.1.11.1
121.1.1.12.1
131.1.1.13.1
141.1.1.14.1
151.1.1.15.1
Sheet2

Alan,
When the IP boundary is reached eg:x.x.1.255 the following IP should be x.x.2.0.
How can this code be amended to incorparate ip boundaries?

Cheers
Colin.

Hi colin

One option would be to amend the code:

Code:
``````Option Explicit
Public pRow As Long
Public pWS As Worksheet
Sub IPCombos()
'Sheet1 column A = Start IP address (format 111.222.333.444.555)
'Sheet1 column B = End IP address (format 111.222.333.444.555)
'Sheet2 column A is cleared & will then be populated with the ip addresses
Dim R As Range, WS As Worksheet

Set WS = Sheets("Sheet1")
pRow = 0
Set pWS = Sheets("Sheet2")
pWS.Columns("A").ClearContents
For Each R In WS.Range("A1:A" & WS.Cells(Rows.Count, "A").End(xlUp).Row)
IPNext R.Text, R.Offset(0, 1).Text, 0
Next R
End Sub
Sub IPNext(ByVal IPStart As String, _
ByVal IPEnd As String, _
ByVal Index As Integer)
Dim iMax As Integer, iPtr As Integer, iLen As Integer
Dim iCount As Integer, iStart As Integer, iEnd As Integer
Dim vStart As Variant, vEnd As Variant

vStart = Split(IPStart, ".")
vEnd = Split(IPEnd, ".")

iMax = UBound(vEnd)
If iMax > UBound(vStart) Then iMax = UBound(vStart)
iStart = Val(vStart(Index))
iEnd = Val(vEnd(Index))
If iEnd < iStart Then iEnd = iStart
For iCount = iStart To iEnd
vStart(Index) = iCount
IPStart = Join(vStart, ".")
If WorksheetFunction.CountIf(pWS.Columns("A"), IPStart) = 0 Then
pRow = pRow + 1
pWS.Cells(pRow, "A").Value = IPStart
End If
For iPtr = Index + 1 To iMax
IPNext IPStart:=IPStart, IPEnd:=IPEnd, Index:=iPtr
Next iPtr
Next iCount
End Sub``````
This takes the start IP address from column A & the end IP address from Column B
So sheet1 cell A1 and B1 would contain 1.1.1.1.0 and 1.1.1.1.255
and cells A2,B2 would contain 1.1.1.2.0 and 1.1.1.2.255

Not a full answer I know, but ...

Cheers Alan,
That can get me started. I can see the project will be finished before I can complete this workbook.

Thanks again,

Colin.

Replies
0
Views
180
Replies
0
Views
241
Replies
1
Views
671
Replies
1
Views
399
Replies
4
Views
205

1,203,096
Messages
6,053,516
Members
444,669
Latest member
Renarian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back