get data from one sheet list in another

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

Please Login or Register  to view this content.

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

When I step through the code it does not show anything being assigned to nodupes

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

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:

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

I was able to include just the code needed to do the relist explaination above

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

Scott,
Paste this into your sheet module in place of the code you posted 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 you want.

Please Login or Register  to view this content.

Brendon

It is a module level variable as it is declared at the top of the module. I did this because the function that fills myArray is outside of the sub that calls it. I'm not sure why it's giving you a subscript out of range error. Make sure that you have the declaration at the top 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 Address sheet?

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...

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?

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 a Worksheet_Change event to see if the value in the cell has been changed and then this can test which cell was changed and if you want the change to be stored in your 'database' (Sites sheet) then you will have to change the appropriate cell on the Sites sheet.
Alternatively if you want to change the part of the IP address then I might suggest protecting any cells (C10:T27) that have a reference to the four main IP address they are refrencing. That will keep the user from changing a cell that is actually calculating an IP rather for direct input.
Again if part of the main IP is changed you will most likey want that reflected on your Sites sheet and can do that with a Worksheet_Change event.

Originally Posted by swieduwi

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?

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