How to get next IP in range from Excel

Question

I know there has to be a way:

I need help getting any hot excel module/trick/script/macro that would look at a range of IPs and give me the next address up? e.g. (192.168.1.128) --> (192.168.1.129)

Not sure i need sub nets, but will throw it out there as well. ( 192.168.1.255) --> ( 192.168.2.1)

I bet is has to be done with vbscript and quick and dirty tutorials on how to implement this would be stellar

Thanks in advance~

EDIT:

excel column D has 192.168.1.1 which was manually entered

excel column E should have 192.168.1.2

I want to avoid someone manually entering that

what is the best way to accomplish this?

Answer 1

you need to split your ip address in blocks

Dim ip As String
Dim ip_addr() As String
Dim ip_next As String
ip = "192.168.1.1"

ip_addr = Split(ip, ".")

ip_next = ip_addr(0) & "." & ip_addr(1) & "." & ip_addr(2) & "." & Trim(Str(Val(ip_addr(3) + 1)))

This does not account subnets however, this is much trickier to implement because you need bitmasks. If you stick to multiples of 8 bits masks this shouldn't be too complicated to handle.

8 bits masks range from 1-255 for each block.

Just check if

Val(ip_addr(3) + 1))) > 255

then handle it properly

EDIT

to read data from a cell in excel simply change

ip = "192.168.1.1"

to

ip = Range("B1")

Answer 2

I don't know what you want to do with such a list, but don't forget that some of the values are particular ones.

You'd better have a look at http://en.wikipedia.org/wiki/IP_address to get more information on that.

Answer 3

Based off the help from Eric: and an Article I found here is the answer:

Function GetNextIP(text As Variant)


Dim ip As String
Dim ip_addr() As String
Dim ip_next As String
ip = text

ip_addr = Split(ip, ".")


ip_next = Trim(Str(Val(ip_addr(3) + 1)))

If ip_next = 256 Then
    ip_next = 0
End If



GetNextIP = ip_addr(0) & "." & ip_addr(1) & "." & ip_addr(2) & "." & ip_next



End Function