Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

Use the following UDFs:

Function upOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) + 1
upOne = Join(s, ".")
End Function


Function downOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) - 1
downOne = Join(s, ".")
End Function


--
Gary''s Student - gsnu200786


"mcmilja" wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

I don't know squat about coding Gary, but I don't believe, by looking at
your UDF, that you took into consideration that an octet can *only* go to
255.

So,
172.15.255.255
plus 1, would be
172.16.0.0

Subtraction would also encompass similar multi-faceted calculations.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gary''s Student" wrote in message
...
Use the following UDFs:

Function upOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) + 1
upOne = Join(s, ".")
End Function


Function downOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) - 1
downOne = Join(s, ".")
End Function


--
Gary''s Student - gsnu200786


"mcmilja" wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit
for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell
A2 to
subtract 1 digit which would result in 172.24.137.148 and another
function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

On Wed, 14 May 2008 08:07:02 -0700, mcmilja
wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

You could try this UDF which will add (or subtract if you enter a negative
number for the second argument) any value from your IP address:

It can probably be simplified but it's late.

To use this, enter the function =AddIP(IP, Add) in some cell where IP is, or
refers to a cell reference containing, an IP address; and Add is the amount you
want to add or subtract.

A2: =AddIP(B2,-1)
C2: =AddIP(B2,1)

To enter this, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

The routine does not check that your original IP address is what is normally
considered valid; nor does it convert them.

================
Option Explicit
Function AddIP(IP As String, Add As Integer) As Variant
Dim aIP As Variant
Dim dWord As Double
Dim iIP As Variant
Dim i As Long

aIP = Split(IP, ".")
'convert to dWord and Add
For i = 0 To 3
dWord = dWord + aIP(i) * 256 ^ (3 - i)
Next i
dWord = dWord + Add

If dWord = 256 ^ 4 Then
AddIP = CVErr(xlErrNum)
Exit Function
End If

aIP(0) = Int(dWord / 256 ^ 3)
aIP(1) = Int((dWord - aIP(0) * 256 ^ 3) / 256 ^ 2)
aIP(2) = Int((dWord - aIP(0) * 256 ^ 3 - aIP(1) * 256 ^ 2) / 256)
aIP(3) = dWord - aIP(0) * 256 ^ 3 - aIP(1) * 256 ^ 2 - aIP(2) * 256

AddIP = Join(aIP, ".")
End Function
============================
--ron

On Thu, 15 May 2008 00:17:09 -0400, Ron Rosenfeld
wrote:

On Wed, 14 May 2008 08:07:02 -0700, mcmilja
wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

You could try this UDF which will add (or subtract if you enter a negative
number for the second argument) any value from your IP address:

It can probably be simplified but it's late.

To use this, enter the function =AddIP(IP, Add) in some cell where IP is, or
refers to a cell reference containing, an IP address; and Add is the amount you
want to add or subtract.

A2: =AddIP(B2,-1)
C2: =AddIP(B2,1)

To enter this, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.


This routine is a little simpler, and also checks that your octets are not
greater than 255:

===================================
Option Explicit
Function AddIP(IP As String, Add As Integer) As Variant
Dim aIP As Variant
Dim dWord As Double, dTemp As Double
Dim i As Long

aIP = Split(IP, ".")
'convert to dWord and Add
For i = 0 To 3
If aIP(i) 255 Then GoTo ErrExit
dWord = dWord + aIP(i) * 256 ^ (3 - i)
Next i
dWord = dWord + Add

If dWord = 256 ^ 4 Then GoTo ErrExit

For i = 0 To 3
If i 0 Then
dWord = dWord - aIP(i - 1) * 256 ^ (4 - i)
End If
aIP(i) = Int(dWord / 256 ^ (3 - i))
Next i

AddIP = Join(aIP, ".")
Exit Function
ErrExit: AddIP = CVErr(xlErrNum)
End Function
=============================
--ron

Hello Ron,

I followed your procedure below but it did not work for me. Any thoughts?

Thanks,
Jaret

"Ron Rosenfeld" wrote:

On Thu, 15 May 2008 00:17:09 -0400, Ron Rosenfeld
wrote:

On Wed, 14 May 2008 08:07:02 -0700, mcmilja
wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

You could try this UDF which will add (or subtract if you enter a negative
number for the second argument) any value from your IP address:

It can probably be simplified but it's late.

To use this, enter the function =AddIP(IP, Add) in some cell where IP is, or
refers to a cell reference containing, an IP address; and Add is the amount you
want to add or subtract.

A2: =AddIP(B2,-1)
C2: =AddIP(B2,1)

To enter this, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.


This routine is a little simpler, and also checks that your octets are not
greater than 255:

===================================
Option Explicit
Function AddIP(IP As String, Add As Integer) As Variant
Dim aIP As Variant
Dim dWord As Double, dTemp As Double
Dim i As Long

aIP = Split(IP, ".")
'convert to dWord and Add
For i = 0 To 3
If aIP(i) 255 Then GoTo ErrExit
dWord = dWord + aIP(i) * 256 ^ (3 - i)
Next i
dWord = dWord + Add

If dWord = 256 ^ 4 Then GoTo ErrExit

For i = 0 To 3
If i 0 Then
dWord = dWord - aIP(i - 1) * 256 ^ (4 - i)
End If
aIP(i) = Int(dWord / 256 ^ (3 - i))
Next i

AddIP = Join(aIP, ".")
Exit Function
ErrExit: AddIP = CVErr(xlErrNum)
End Function
=============================
--ron

On Mon, 16 Jun 2008 14:03:05 -0700, mcmilja
wrote:

Hello Ron,

I followed your procedure below but it did not work for me. Any thoughts?


I have plenty of thoughts :-)

What does "did not work" mean? And what did you do?

If *I* follow the procedure, it works fine.
--ron