Solved: Amend and Move

[Bob Phillips]

Gerry,

Just to let you know, I am haviing real problems testing this as I don't have the calendar control on this machine. When I downloaded it, it wouldn't register as the OS is 64 bit (you might want to think about that). I will need to try it on another 32 bit machine later.

For my info, you have Search_****_Finance sheets that has code that moves completed from Database to Finance Passed, and Search_***_Complete that has code that moves completed from Finance Passed to Completed. Is this the actual flow?

[Hoopsah]

Hi Bob,

yes, I was trying to do it so that the records originally come in to the workshhet: Database.

The user will search this worksheet and add a date when it has passed,

This will move the record from Database to Finance Passed.

Same thing for the next search that will read the record from Finance passed and move it to completed.

If you don't think that is the best way, as I am also trying to work on something that will show time passed between the dates, but I am struggling as I have to read 2 seperate worksheets, then maybe you could advise.

But at the moment, yes, Database to Finance Passed to Completed

I have attached another copy where I have removed the calendar control

Cheers

Gerry

[Bob Phillips]

I must be missing something Gerry, because I just completed one on Search_Developer_Complete, and the formulas were all reset as they should be, so the remaining data on Finance Passed was shown fine.

[Hoopsah]

Hi Bob,

hope I can explain this properly.

When you complete a record in Search_Developer_Complete everything on that page is fine.

But when you then look in the other tabs, the formula has went from Database!$B$1:$B$500=$H$6 to Database!$B$1:$B$499=$H$6 and this continues to count down each time a record is removed.

I access each page through a macro attached to a button, would it be feasible to force the correct formula into the cells at this time?

I'm afraid I am really scratching my head this time and beginning to wonder if I might be better starting again!!

[Bob Phillips]

Of course, it is bloody obvious now that you explain it Gerry, I shouldn't have been bright enough to work that out once I realised that you had changed it to multiple entry sheets.

Don't start again, this is easily fixable, but I also have to change it so that you don't have identical code behind multiple worksheets (sorry, it offends my aesthetical view of coding ), so give me a bit of time.

[Hoopsah]

Take as much time as you need Bob.

I am still tinkering with it but not really getting any further

[Bob Phillips]

Gerry, why do you have three sheets, Ref Dev and Team for Finance and Complete? I am looking at Finance, and one item from database is on both Team and Dev. Is that what is required?

[Hoopsah]

Hi Bob

I will attach everyhting that I have done so far.

The original idea was I uploaded from 4 seperate workbooks and all the data should be copied to the tab "Database"

There are 3 ways to search for the record, By Reference, By Team or by Developer.

Once you input a date, it will move the record from Database to Finance_Passed.

Again 3 ways to search the Finance_Passed data, then when the date is added it will move the record to Completed.

This is where I am wondering if this is a good idea now.

Perhaps if I have just one database and when you input a date it just updates a colum, then have search results just display the appropriate data. I thought at first it would be better to have them all seperated but not so sure now!!

[Bob Phillips]

How about this?

One database sheet with a column for Finance Passed date and a column for Completed date.

A dropdown with two values, 'Not Passed', and 'Passed not Completed' or some such.

Another dropdown, Team, Ref and Dev.

An input cell for the Team, Ref or Dev to filter by.

The items shown would be dependent upon all three selected values.

I think this is neater than how it is at present, and makes maintenance more manageable.

[Hoopsah]

Got to agree Bob,

It is only as it starting increasing in size that I figured it wasn't the best way of approaching it.

Think I will have another go.

Thanks for all your help though Bob, I'm sure I will be seeking you out again soon

[Bob Phillips]

Do you want me to make those changes?

[Hoopsah]

Seriously!!

To be honest Bob I would love you too as I know you would do a much better job in a fraction of the time - but I wouldn't want to put you out either.

If you have time, I would be extremely grateful though

Cheers Bob

[Bob Phillips]

Okay, I will get to it. I am going to a SQLBits tomorrow, so I probably will get it out on Sunday or Monday - OK?

[Bob Phillips]

Gerry,

Here is the amended file. I hope it is what you expected, I think it has turned out well, and I am pleased with it.

I am not sure how many actual items this will have to cater for, but there is a formula on the Workings sheet where I build a dynamic list of references for use in the Search For dropdown should Reference be selected in the Search By dropdown. This list goes down to row 500, so you may need to extend it and also to update the formula in column A on the Search worksheet.

[Hoopsah]

WOW Bob,

Looks good.

I will need to have a wee play with it and try to understand some of your coding first.

It looks fantastic though.

Once again, I can't thank you enough for this, means I will be able to get this project finished and rolled out this week.

Thanks again Bob

Gerry

[Bob Phillips]

Gerry,

The code hasn't changed much, actually it is a a tad simpler; and of course there is only one instance of that code now. There is no need to rebuild the formula, the one that went bad when you went multi-search, as nothing ever gets deleted any longer. It is also easier to test, rather than have to move rows from Complete back to Finance Passed back to Database, you just delete the date(s) in the Database sheet .

The big change is that the formula on the Search sheet is much more complex, not more complicated just more things to test in there.

[Hoopsah]

A Tad simpler!!

Makes mine look like War and Peace.

Hate to ask this though as I should probably know by now, but whenever I click on a cell within the new Search tab I get an error pop-up, Comple Error: Variable Not Defined.

However, if the VB Editor is open then the worksheet is fine and everything works great. Do you know why this would be?

[Bob Phillips]

Gerry,

I started out by commenting out some code out before I removed it completely. As my main changes were in the Worksheet_Change procedure, I removed the coomented code before posting. I also had some code in the Worksheet_SelectionChange procedure commented out, some because of the above reason, but also your Open Calendar call because of reasons stated previously, but in this procedure I just uncommented everything (), including the code that should have been deleted.

I think if you change that procedure to the following code you will overcome it

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then

If Not Intersect(Target, Me.Range("_searchComplete")) Is Nothing Then

Call OpenCalendar

Me.Range("_searchComplete").Activate
Selection.ClearContents
Me.Range("_searchType").Select
End If
End If
End Sub
[/vba]

BTW, one thing I did not mention is that I didn't test anything that adds to the Database. I would hope this has not been affected, but you should check it out.

[Hoopsah]

Fantastic!!

The compile error is gone.

I have tried adding records to the database and it adds fines and is available immedietly in the search. Just remains for me to add some calculations to produce the stats and this is ready to go.

Can't thank you enough Bob, this has been an amazing help.

I am also looking forward to trying to disect your code and hopefully be able to use it in upcoming projects.

Again, Thanks Bob you are a star!

[Bob Phillips]

<snip>

Just remains for me to add some calculations to produce the stats and this is ready to go.

<snip>

Have you used pivot tables Gerry?