Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

Function IsIPAddress(strIP As String) As Boolean

'4 numbers from 0 to 255 separated by dots?
'------------------------------------------
Dim i As Long
Dim n As Long
Dim arrSplit
Dim arrByte() As Byte

On Error GoTo ERROROUT

arrSplit = Split(strIP, ".")

If UBound(arrSplit) < 3 Then
Exit Function
End If

For i = 0 To 3

If Len(arrSplit(i)) 3 Then
Exit Function
End If

If CLng(arrSplit(i)) 255 Then
Exit Function
End If

arrByte() = arrSplit(i)
For n = 0 To UBound(arrByte) - 1 Step 2
If arrByte(n) < 48 Or arrByte(n) 58 Then
Exit Function
End If
Next n

Next i

IsIPAddress = True

ERROROUT:

End Function



RBS


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Sep 2009 09:40:03 -0700, HarryisTrying
wrote:

I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007

Are the IP addresses the only contents of the cell, or is there more than
just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a
valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron