Having problems with this vb to display a list of specific cell data
from "Sites" sheet on "Addressing" sheet.


Code:
--------------------

Sub CreateSitesList()

Sheets("Addressing").Range("V2:W40").ClearContents
Lastrow = Sheets("Sites").Range("c65536").End(xlUp).Row

For Each ce In Sheets("Sites").Range("C2:C" & Lastrow)
On Error Resume Next
nodupes.Add Item:=ce, key:=CStr(ce)
Next ce

For i = 1 To nodupes.Count
Sheets("Addressing").Range("V2").Offset(i, 0).Value = nodupes(i)
Sheets("Addressing").Range("V2").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("c 1:c" & Lastrow), nodupes(i))
Next i

End Sub

--------------------


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

How are you using nodupes in this code? Are you using this as
collection?

When I step through the code it does not show anything being assigne
to nodupe

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38019

Hello Swieduwi,

You didn't delcare "nodupes" as a collection. You never see an error
because of the "On Error Resume Next" statement, which you need to
check for duplicates in the collection.

Add Dim nodupes As New Collection at the begining of your code.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=380191

Any way to streamline this code, it's a bit bulky


Code:
--------------------

Sub CreateSitesList()

Dim Sites As New Collection
Dim ASNno As New Collection
Dim idate As New Collection

Sheets("Addressing").Range("V2:W40").ClearContents
Sitesrow = Sheets("Sites").Range("C65536").End(xlUp).Row
ASNrow = Sheets("Sites").Range("M65536").End(xlUp).Row
iDaterow = Sheets("Sites").Range("A65536").End(xlUp).Row

For Each sr In Sheets("Sites").Range("C2:C" & Sitesrow)
On Error Resume Next
Sites.Add Item:=sr, key:=CStr(sr)
Next sr

For Each ar In Sheets("Sites").Range("M2:M" & ASNrow)
On Error Resume Next
ASNno.Add Item:=ar, key:=CStr(ar)
Next ar

For Each id In Sheets("Sites").Range("A2:A" & iDaterow)
On Error Resume Next
iDate.Add Item:=id, key:=CStr(id)
Next id

For i = 1 To SiteList.Count
Sheets("Addressing").Range("V2").Offset(i, 0).Value = Sites(i)
Sheets("Addressing").Range("W2").Offset(i, 0).Value = iDate(i)
Sheets("Addressing").Range("X2").Offset(i, 0).Value = ASNno(i)
Next i

End Sub
--------------------


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

There is a problem with this code, it's not allowing duplicate dates
to show up on the list

Can anyone help me figure this out?
Code is listed above

--
swieduw
-----------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...fo&userid=2196
View this thread: http://www.excelforum.com/showthread.php?threadid=38019

You are missing duplicate dates because a property of using collections
is that it is necessary to have unique keys for each item added to the
collection. If you have duplicate dates then you key assignments set
up duplicate keys.
You are not seeing this error because of the 'On Error Resume Next'

If you don't need the key to access the members of your collection then
you can eliminate this from your coding.

idate.Add Item:=ir, key:=CStr(ir)

becomes

idate.Add Item:=ir

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380191

Could you help me with the rest of this code ?
If so, :) Here is the scoop..

The first part creates a list of sites from a sheet called "Sites"
(Future will be a CSV File not a sheet) to my main workbook sheet
called "Addressing"

this part re-fills all fields when a user clicks on the site list
"name"

For instance, you see a sheet that is a data entry area, standard stuff
like
site name, addresss, contacts ect.. and IP addresses to the right there
is a list of sites that are complete. If I want to see info from a
previous site, I would click on the site name and all the fields would
populate with the data from the "Sites" sheet.

Does that make any sense?

So this is what I have come up with, (of course it's not working)
"Damit Jim, I am a Network Engineer not a programmer", sorry..star trek
humor

Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Relist As New Collection

If Not Intersect(Target, Range("E6:E40")) Is Nothing Then
Range("H6:I300").ClearContents
Lastrow = Sheets("Sites").Range("e65536").End(xlUp).Row

For Each ce In Sheets("Sites").Range("d2:d" & Lastrow)
If ce = Target.Value Then
On Error Resume Next
Relist.Add Item:=ce.Offset(0, 1), key:=CStr(ce.Offset(0, 1))
End If

Next ce
For i = 1 To Relist.Count
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("J 9:J" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("O 9:O" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 2:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 3:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 4:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 5:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 6:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 7:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 8:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 9:D" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("S 2:S" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("S 3:S" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("H 8:H" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("H 9:H" & Lastrow), Relist(i))
Sheets("Addressing").Range("H5").Offset(i, 0).Value = Relist(i)
Sheets("Addressing").Range("H5").Offset(i, 1).Value = WorksheetFunction.CountIf(Sheets("Sites").Range("D 29:D57" & Lastrow), Relist(i))

Next i
End If
End Sub

--------------------


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

I was able to include just the code needed to do the relist explainatio
abov

+-------------------------------------------------------------------
|Filename: relist.zip
|Download: http://www.excelforum.com/attachment.php?postid=3530
+-------------------------------------------------------------------

--
swieduw
-----------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...fo&userid=2196
View this thread: http://www.excelforum.com/showthread.php?threadid=38019

Scott,

First, aren't your completed sites listed in cells W3:W59 (or where
ever the end of the list will be)? Your selection change is currently
triggered by E6:E40.
I changed this to W3:W59 for testing

Second, as soon as you click on a site name in W3:W59 it tries to clear
the contents of H6:I300 which it can't do because some of those cells
are merged.
I commented this line out for testing

Third, you are comparing the Target.Value of the Site Name selected
with column D on the "Sites" list which is actually the Hosp Name.
I chaged this to compare with column C from your "Site" sheet which
contains the Site Names

Then when you add the item to the Relist Collection what exactly are
you trying to add? The code has only a single item being added to that
collection which corresponds to the Hosp Name.

In the For i to Relist.Count loop I'm not exactly sure what you are
trying to accomplish with this. The destination
Range("H5").Offset(i,0) or
Range("H5").Offset(i,1) seems to correspond to a Range you currently
have populated with the VPN Device Codes (H6) or
the VPN Device Type for that site (I6)

It looks like you may have changed the layout of your sheets and things
aren't in the same locations as when you first wrote the code?

Hope this helps to get you started


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380191

bhofsetz Wrote:

It looks like you may have changed the layout of your sheets and things
aren't in the same locations as when you first wrote the code?

Hope this helps to get you started

I had found this code on a forum that seem to be what I was looking
for, I have been trying to adapt it to my needs, but it's not working.
:(

I just am not good enough to start from scratch so I was trying to
adapt


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

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

Brendon,
Thanks for your reply.

I am having a problem with it.
Error 9
Subscript out of range

at this line
Range("J2") = myArray(0)

I have not been able to determine what is causing the problem
but I think it has somthing to do with the declaration of myArray
Should it be Dim inside the sub or should it be public or global ?


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

It is a module level variable as it is declared at the top of th
module. I did this because the function that fills myArray is outsid
of the sub that calls it. I'm not sure why it's giving you a subscrip
out of range error. Make sure that you have the declaration at the to
of the sheet module.
The only way I got Error 9 is if I put the declaration inside the sub.
You can't declare arrays as Public variables.

Where do the rip1, rip2, rip3 etc. need to be placed on the Addres
sheet

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38019

You are right, Sorry about my placing the Dim statement inside the sub
It was giving me another error before, but it was due to it not being
at the top.
Thanks,
Bare with me being a bone head...


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

a question for ya,

if I select the site and it recalls the data to the screen and I make a
change,
how do I make that change to to the same line ?

Can I store the line number?


--
swieduwi
------------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...o&userid=21962
View this thread: http://www.excelforum.com/showthread...hreadid=380191

I'm not quite sure what you are asking here.
For example if you change one of the input fields, ie. address
contact, NetScreen, etc. then you will probalby want to use
Worksheet_Change event to see if the value in the cell has been change
and then this can test which cell was changed and if you want the chang
to be stored in your 'database' (Sites sheet) then you will have t
change the appropriate cell on the Sites sheet.
Alternatively if you want to change the part of the IP address then
might suggest protecting any cells (C10:T27) that have a reference t
the four main IP address they are refrencing. That will keep the use
from changing a cell that is actually calculating an IP rather fo
direct input.
Again if part of the main IP is changed you will most likey want tha
reflected on your Sites sheet and can do that with a Worksheet_Chang
event.

swieduwi Wrote:

if I select the site and it recalls the data to the screen and I make
change,
how do I make that change to to the same line ?

Can I store the line number?

Which line number do you want to store? The line number on the Addres
sheet where the change was made or the line number where the informatio
was pulled off of the Sites sheet

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38019