Scott,
Paste this into your sheet module in place of the code you poste
earlier.
I'm still not sure what you want done with the rip1, rip2, rip3, etc
that are on the Sites sheet but I think this pretty much does what yo
want.
Code
-------------------
Dim myArray()
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Dim AddSht As Worksheet, SiteSht As Worksheet, SiteRow As Long
Dim SiteIP As String, SiteArray(4) As String
Set AddSht = Sheets("Addressing")
Set SiteSht = Sheets("Sites")
If Not Intersect(Target, Range("W2:W59")) Is Nothing Then
Lastrow = Sheets("Sites").Range("C65536").End(xlUp).Row
For Each ce In SiteSht.Range("C1:C" & Lastrow)
If ce = Target.Value Then
SiteRow = ce.Row
With SiteSht
Range("J9:M9") = .Range("A" & SiteRow)
Range("O9:R9") = .Range("B" & SiteRow)
Range("D2:E2") = .Range("C" & SiteRow)
Range("D3:E3") = .Range("D" & SiteRow)
Range("D4:E4") = .Range("E" & SiteRow)
Range("D5:E5") = .Range("F" & SiteRow)
Range("D6:E6") = .Range("G" & SiteRow)
Range("D7:E7") = .Range("H" & SiteRow)
Range("D8:E8") = .Range("I" & SiteRow)
Range("D9:E9") = .Range("J" & SiteRow)
SiteIP = .Range("K" & SiteRow)
RipIP (SiteIP)
Range("J2") = myArray(0)
Range("L2") = myArray(1)
Range("N2") = myArray(2)
Range("P2") = myArray(3)
Range("R2") = myArray(4)
SiteIP = .Range("L" & SiteRow)
RipIP (SiteIP)
Range("J3") = myArray(0)
Range("L3") = myArray(1)
Range("N3") = myArray(2)
Range("P3") = myArray(3)
Range("R3") = myArray(4)
Range("H8:I8") = .Range("M" & SiteRow)
Range("H9:I9") = .Range("N" & SiteRow)
'Where exactly do the rip1, rip2 etc go on the Address sheet?
End With
Exit For
End If
Next ce
End If
End Sub
Sub RipIP(SiteIP)
ReDim myArray(4)
Dim i As Integer
i = 0
For x = 1 To Len(SiteIP)
If Mid$(SiteIP, x, 1) = "." Or Mid$(SiteIP, x, 1) = "/" Then
i = i + 1
ElseIf Mid$(SiteIP, x, 1) < "." Then
myArray(i) = myArray(i) & Mid$(SiteIP, x, 1)
End If
Next x
End Su
-------------------
Brendo
--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...fo&userid=1880
View this thread:
http://www.excelforum.com/showthread.php?threadid=38019