Prev: moved code to new book - some doesn't work now
Next: Initialise the registry values in a array and retrive the values as required
From: deen on 25 Apr 2010 23:25
Hi Every One,

I hope you all are doing well, I required your help to resolve my
issue in
excel.

On single worksheet i have two different sheet name as 1.IP Range 2.
Result.

Question:
EG:IP Range

Start IP End IP Area

192.168.1.4 195.182.254.254 AG
10.15.33.10 10.18.56.254 EMEA
10.128.33.5 10.132.40.60 AP

Here it will Continued as well.

On Result sheet i required result like:

IP Area

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 EMEA
10.170.255.255 NA
10.129.36.8 AP

I required result on area column. Here we have more than 50000 IP's
but very
difficult to find the area.

Could any one please help me on this issue. How i can resolve the
issue

Thanks in advance
Deen
From: joel on 26 Apr 2010 07:28

Are each of your areas unique or are there overlapping areas?

for eample
Area A : 190.0.0.0 to 199.255.255.255
Area B : 192.0.0.0 to 192.255.255.255

This code works for non-over lapping IP addresses. I would have to
modify the code to test for best match.

Sub SplitIP()

LookupIP = "192.170.30.30"

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
IP1 = Split(.Range("A" & RowCount), ".")
IP2 = Split(.Range("B" & RowCount), ".")
Area = .Range("C" & RowCount)
With Sheets("Sheet2")
For Index = 0 To 3
.Range("A" & RowCount).Offset(0, Index) = Val(IP1(Index))
.Range("E" & RowCount).Offset(0, Index) = Val(IP2(Index))
Next Index
.Range("I" & RowCount) = Area
End With
Next RowCount
End With

'sort IP address
With Sheets("Sheet2")
.Rows("1:" & LastRow).Sort _
Header:=xlNo, _
key1:=.Range("D1"), _
Order1:=xlAscending
.Rows("1:" & LastRow).Sort _
Header:=xlNo, _
key1:=.Range("A1"), _
Order1:=xlAscending, _
key2:=.Range("B1"), _
Order2:=xlAscending, _
key3:=.Range("C1"), _
Order3:=xlAscending

LookupIPArray = Split(LookupIP, ".")
Found = "Low"
RowCount = 1
Do While RowCount <= LastRow
For Index = 0 To 3
Field = Val(LookupIPArray(Index))
If .Range("A" & RowCount).Offset(0, Index) < Field Then
Exit For
End If

If Field < .Range("E" & RowCount).Offset(0, Index) Then
Found = "Match"
Area = .Range("I" & RowCount)
Exit For
End If

If Field > .Range("E" & RowCount).Offset(0, Index) Then
Found = "High"
Exit For
End If

'required if IP exactly matches highest address in range
If Index = 3 Then
Found = "Match"
Area = .Range("I" & RowCount)
End If
Next Index
If Found = "High" Or _
Found = "Match" Then

Exit Do
End If
RowCount = RowCount + 1
Loop

If Found = "High" Then
MsgBox ("IP not found : " & LookupIP)
End If
If Found = "Match" Then
MsgBox ("Area : " & Area)
End If

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198408

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: moved code to new book - some doesn't work now
Next: Initialise the registry values in a array and retrive the values as required