View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson
 
Posts: n/a
Default How do create a formula to add IP addresses?

Try

Function NextIPAddress(IP As String) As String
Dim Arr As Variant
Dim V As Integer
Arr = Split(IP, ".")
V = CInt(Arr(UBound(Arr)))
V = V + 1
If V Mod 4 = 0 Then
Arr(UBound(Arr)) = V
Else
V = V + 4 - (V Mod 4)
Arr(UBound(Arr)) = CStr(V)
End If
NextIPAddress = Join(Arr, ".")
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jeffreyj" wrote in message
...
Chip,

Say I wanted to add onto this function, and instead of just
giving the next
IP address, I want to check to see if the next IP address is
divisible by 4.
If it is divisible by 4 then put it in the cell, if not, put
the next
available address that is divisible by 4. How would I do this?
thanks.


"Chip Pearson" wrote:

I'd use a VBA function.

Function NextIPAddress(IP As String) As String
Dim Arr As Variant
Arr = Split(IP, ".")
Arr(UBound(Arr)) = Arr(UBound(Arr)) + 1
NextIPAddress = Join(Arr, ".")
End Function

You can then call this from a worksheet cell with code like
=NextIPAddress(A1)

You be using Excel 2000 or later in order to use the Split and
Join functions.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jeffreyj" wrote in
message
...
I am trying to make a formula that adds the last part of an
IP
address, such
that when I type in one IP address suchas 147.202.70.132,
they
automatically
fill in below sequentially with 133, 134, 135, etc. Can
anyone help me with
this? Thanks.