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

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

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