Issues with arrays/collections/etc

[Sissyfoo]

Hello,

I was wondering if anyone could help me out with this problem. At first it seems quite simple but it is driving me up the wall.

Basically, I have 4 sheets with staff names and company names in columns A and B. The 4 sheets represent financial quarters so, for the most part, the staff and company names are the same on all 4 sheets but occassionaly someone is added or removed (because they got their lazy butts fired). I also have a sheet at the end called 'Brickwall' which is intended as a sort of commentary sheet with the staff and company names in columns A and B and then another 13 or so columns (up to column O) which contain random numbers or commentary.

What I wanted to do was write a script that would sort through all 4 quarter sheets and extract the unique names and place them all in brickwall sheet in alphamabetical order. I have a nice little collection script which does that rather nicely. Unfortunately, doofus that I am, the brickwall sheet only updates columns A and B and leaves C - O alone. This means that if someone adds a new company or staff name in one of the quarters and then runs the macro, a new row will be inserted into columns A + B but NOT for the rest of the columns. This means the pre-existing data in columns C - O will very quickly go out of sync with the data in columns A and B.

I tried to remedy this by expanding the macro to include all 15 columns but this didn't work. I then wrote more code to ... well, let's just say that by the time I got it to work properly the script had become the most bloated and diseased pile of crap you will EVER see. Running the macro took a good 10 seconds and it was, in essence, useless because it didn't really act as a time saver but more of a hinderance.

Does anyone have any ideas on what I could do to make this work? I am completely stumped.

[Dave]

Why bother? http://www.vbaexpress.com/forum/showthread.php?t=6173 Dave

[Sissyfoo]

Why bother? http://www.vbaexpress.com/forum/showthread.php?t=6173 Dave

Mainly because I want to know if it can be done and some tips on how to approach it...

[Dave]

I guess my point is that you were provided with help before but you didn't make an effort to use it... or at least your lack of a response in the link seems to indicate this. I wish you luck in solving this thread. Perhaps others will be more willing to contribute. Dave

[Sissyfoo]

Too be honest, I had completely forgotten about that thread. I tried out some of the suggestions but after about 20 minutes of testing various ideas with the intention of reporting on how they worked out I got dragged into a meeting and was informed that that aspect of the project was now scrapped and then got loaded down with another project to work on. By the time I got back to my desk I had totally forgotten about this thread and was thinking about my new project. I wasn't being rude or ungrateful...just absent minded. Apologies.

James

[Dave]

How about this? Before this code...
[VBA]Range("a7:b60").End(xlDown).ClearContents

Range("a7").Resize(UBound(outarr), 2).Value = outarr[/VBA]

Compare the contents of array "outarr" 's to the existing ws range in "Brickwall" for changes.
Then remove/add the entire row to the ws before placing your new data in "A" & "B"
Then expand your sort range to include the entire contents of "Brickwall". HTH Dave
edit...don't know what that font thing is?

[johnske]

Hi Dave,

I edited your post to correct that font error - it's because whatever document of yours you copied from had a font change or two somewhere in it.
To correct this if it happens again, select ALL your post, then choose Verdana font and size 2.

Regards,
John

[Sissyfoo]

That's a good idea. I'll give that a try when I'm back in the office on Monday (why the hell doesn't M$ works not come with a VB editor, Bill, huh?!).

Watch this space for an update!