IP Adrress Allocation

[rs2k]

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
	A	B	C	D	E	F
1	Building	Floor	Room	TAP	VLAN	IP Address
2	1	G	X	2
3	7	G	X	3
4	2	G	X	2
5	3	1	X	1
6	1	1	X	2
7	5	G	X	1
8	2	G	X	2
Sheet1

I want the data to end up like,

Book1
	A	B	C	D	E	F
10	Start IP	End IP
11	10.1.1.1	10.1.1.254
12
13	Building	Floor	Room	TAP	VLAN	IP Address
14	7	G	X	3		10.1.1.1
15	1	G	X	2		10.1.1.2
16	1	1	X	2		10.1.1.3
17	2	G	X	2		10.1.1.4
18	2	G	X	2		10.1.1.5
19	3	1	X	1		10.1.1.6
20	5	G	X	1		10.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.

 

[al_b_cnu]

Hi colin, in true 'Blue Peter' tradition, here's something I prepared earlier:

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

 

[rs2k]

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.

 

[rs2k]

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

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

Cheers
Colin.

 

[al_b_cnu]

Hi colin, looks good to me

Just re-tested on a new sheet, with this input:

Book1
	A	B	C	D
1	1.1.1.1.1
2	1.1.1.15.1
Sheet1

which, after running IPCOMBOS gives this result:

Book1
	A	B	C	D
1	1.1.1.1.1
2	1.1.1.2.1
3	1.1.1.3.1
4	1.1.1.4.1
5	1.1.1.5.1
6	1.1.1.6.1
7	1.1.1.7.1
8	1.1.1.8.1
9	1.1.1.9.1
10	1.1.1.10.1
11	1.1.1.11.1
12	1.1.1.12.1
13	1.1.1.13.1
14	1.1.1.14.1
15	1.1.1.15.1
Sheet2

 

[rs2k]

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.

 

[al_b_cnu]

Hi colin

One option would be to amend the 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 ...

 

[rs2k]

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

Thanks again,

Colin.