PDA

View Full Version : Solved: Amend and Move



Hoopsah
03-15-2012, 02:52 AM
Hi

I currently have a spreadsheet where, if I type in a reference number it will bring in all the instances of the records from another sheet to display.

I want to be able to amend the details then send it back to the original worksheet, however, If 1 column has been updated to show job complete then it should move the record into a new sheet, and show the date that it was updated.

I have been having limited success with the amendments but I am getting confused as there can be quite a few seperate records on display

Hope this makes sense - I have attached a copy of the sheet so far,

Any help will be welcomed.

Bob Phillips
03-15-2012, 04:32 AM
Add this code to your edit sheet code module



Option Explicit

Private thisFormula As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsDB As Worksheet
Dim dbId As Long
Dim dbRow As Long
Dim wsComp As Worksheet
Dim compRow As Long

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False

Set wsDB = Worksheets("Database")
Set wsComp = Worksheets("Completed")

dbId = Me.Cells(Target.Row, "A").Value
dbRow = Application.Match(dbId, wsDB.Columns(1), 0)

If Not Intersect(Target, Me.Range("C10:I24")) Is Nothing Then

With Me.Rows(Target.Row)

.Cells(1, "C").Copy
wsDB.Cells(dbRow, "B").PasteSpecial Paste:=xlValues
.Cells(1, "D").Resize(, 2).Copy
wsDB.Cells(dbRow, "D").PasteSpecial Paste:=xlValues
.Cells(1, "F").Copy
wsDB.Cells(dbRow, "G").PasteSpecial Paste:=xlValues
.Cells(1, "G").Copy
wsDB.Cells(dbRow, "F").PasteSpecial Paste:=xlValues
.Cells(1, "H").Resize(, 2).Copy
wsDB.Cells(dbRow, "H").PasteSpecial Paste:=xlValues
End With

Target.Formula = thisFormula
ElseIf Not Intersect(Target, Me.Range("J10:J24")) Is Nothing Then

If IsDate(Target.Value) Then

compRow = wsComp.Cells(wsComp.Rows.Count, "A").End(xlUp).Row + 1
wsDB.Rows(dbRow).Copy wsComp.Cells(compRow, "A")
wsComp.Cells(compRow, "J").Value = Date
wsComp.Cells(compRow, "J").NumberFormat = "dd/mm/yyyy"
wsDB.Rows(dbRow).Delete
Target.Value = ""
End If
End If

ws_exit:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Range("C10:I24")) Is Nothing Then

thisFormula = Target.Formula
End If
End Sub

Hoopsah
03-15-2012, 04:48 AM
Hi Bob

you will need to forgive me here.

I have copied the code into the worksheet, but I can't see what it is doing or how to make it respond.

Sorry if I am being thick!

Bob Phillips
03-15-2012, 05:39 AM
Gerry,

If you have put that code in the correct worksheet module, behind the Edit sheet, just change any value in C10:I24 on the Edit sheet and it will update the Database sheet. Put a date in J10:J24, and it moves it from Database to Completed and remove it from database.

Hoopsah
03-15-2012, 05:54 AM
Right, I must be being thick!!

I copied the code into the "View Code" of the tab Edit-Existing

I have tried inputting a date and nothing changes.

I have put it in the wrong place?

Bob Phillips
03-15-2012, 06:06 AM
Post your updated workbook Gerry and let me see it.

Hoopsah
03-15-2012, 06:13 AM
Hi Bob

attached the updated version

Bob Phillips
03-15-2012, 06:39 AM
The code is in a standard code module Gerry, not the Edit worksheet code module.

Hoopsah
03-15-2012, 06:47 AM
:dunno

Sorry Bob,

I tried the code in a module too and still couldn't make it do anything!!

Bob Phillips
03-15-2012, 06:54 AM
Take a look at this

Hoopsah
03-15-2012, 06:59 AM
That is fantastic Bob,

I was obviously putting the code in the wrong place again.

Works Perfectly,

Thanks for your help (And your Patience)

Cheers Bob

Marking as Solved.

Bob Phillips
03-16-2012, 08:08 AM
Untested Gerry, but it should be


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Range("C10:I24")) Is Nothing Then
thisFormula = Target.Formula
Exit Sub
ElseIf Target.Address = "$J$10:$J$24" Then

Call OpenCalendar
End If
End Sub

Hoopsah
03-19-2012, 02:31 AM
Hi Bob

Thanks for answering.

Tried the code and I'm afraid the calender doesn't pop up and also if I type in a Date then it clears the whole page.

Bob Phillips
03-19-2012, 03:25 AM
Can you post the workbook Gerry?

Hoopsah
03-19-2012, 05:03 AM
Hi Bob

copy attached.

No Pop up for column J, but when I type in a date the record is moved into the correct tab, but the page then clears and won't re-populate when you type the reference in again.

Cheers

Gerry

Bob Phillips
03-19-2012, 06:05 AM
That was a toughie :)


Option Explicit

Private thisFormula As String

Private Sub Worksheet_Change(ByVal Target As Range)
Const FORMULA_ROW As String = _
"=IF(ISERROR(SMALL(IF((Database!$C$1:$C$500=$H$6),ROW($A$1:$A$500),""""),ROW($A1))),""""," & vbLf & _
"INDEX(Database!$A$1:$A$500,SMALL(IF((Database!$C$1:$C$500=$H$6),ROW($A$1:$A $500),""""),ROW($A1))))"
Dim wsDB As Worksheet
Dim dbId As Long
Dim dbRow As Long
Dim wsComp As Worksheet
Dim compRow As Long

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False

Set wsDB = Worksheets("Database")
Set wsComp = Worksheets("Completed")

dbId = Me.Cells(Target.Row, "A").Value
dbRow = Application.Match(dbId, wsDB.Columns(1), 0)

If Not Intersect(Target, Me.Range("C10:I24")) Is Nothing Then

With Me.Rows(Target.Row)

.Cells(1, "C").Copy
wsDB.Cells(dbRow, "B").PasteSpecial Paste:=xlValues
.Cells(1, "D").Resize(, 2).Copy
wsDB.Cells(dbRow, "D").PasteSpecial Paste:=xlValues
.Cells(1, "F").Copy
wsDB.Cells(dbRow, "G").PasteSpecial Paste:=xlValues
.Cells(1, "G").Copy
wsDB.Cells(dbRow, "F").PasteSpecial Paste:=xlValues
.Cells(1, "H").Resize(, 2).Copy
wsDB.Cells(dbRow, "H").PasteSpecial Paste:=xlValues
End With

Target.Formula = thisFormula
ElseIf Not Intersect(Target, Me.Range("J10:J24")) Is Nothing Then

If IsDate(Target.Value) Then

compRow = wsComp.Cells(wsComp.Rows.Count, "A").End(xlUp).Row + 1
wsDB.Rows(dbRow).Copy wsComp.Cells(compRow, "A")
wsComp.Cells(compRow, "J").Value = Date
wsComp.Cells(compRow, "J").NumberFormat = "dd/mm/yyyy"
wsDB.Rows(dbRow).Delete

Me.Range("A10").FormulaArray = FORMULA_ROW
Me.Range("A10").AutoFill Me.Range("A10:A24")
End If
End If

ws_exit:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then
If Not Intersect(Target, Me.Range("C10:I24")) Is Nothing Then
thisFormula = Target.Formula
Exit Sub
ElseIf Not Intersect(Target, Me.Range("J10:J24")) Is Nothing Then

Call OpenCalendar
End If
End If
End Sub

Hoopsah
03-19-2012, 06:16 AM
Glad you thought so!!

Works perfectly Bob, I have added a line just to clear the contents of column J once a date has been selected, but once again your help has been wonderful.

Thanks again Bob

Gerry

Hoopsah
03-28-2012, 07:02 AM
Hi Bob,

I know I have a cheek asking here, but part of the code doesn't work:

Me.Range("A10").FormulaArray = FORMULA_ROW
Me.Range("A10").AutoFill Me.Range("A10:A24")

When I add a date, the row gets moved to the correct spreadsheet, but the formula:

"=IF(ISERROR(SMALL(IF((Database!$C$1:$C$500=$H$6),ROW($A$1:$A$500),""""),ROW($A1))),""""," & vbLf & _
"INDEX(Database!$A$1:$A$500,SMALL(IF((Database!$C$1:$C$500=$H$6),ROW($A$1:$A $500),""""),ROW($A1))))

on the spreadsheet counts down by 1 - C1:C499 and of course, doesn'y display any data.

Any Ideas?

Bob Phillips
03-28-2012, 07:44 AM
When you say add a date Gerry, do you mean inserting a row on the database workshet, or do you mean inserting a date in column J?

Can you post your latest workbook so we are in sync?

Hoopsah
03-29-2012, 12:26 AM
Hi Bob

thanks for looking at this again.

The problem is within the search tabs.

When you add the date into column J, the record is moved into the appropriate worksheet, but then the formula in column A changes and then the array values are not the same so nothing shows.

I have attached the whole workbook so that you can see everything I am doing, but the problem lies within each search tab - Search_Developer_Complete, Search_Ref_Complete, Search_Team_Complete etc.

Cheers Bob

Bob Phillips
03-29-2012, 02:07 AM
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
03-29-2012, 02:50 AM
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
03-29-2012, 02:33 PM
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
03-30-2012, 12:54 AM
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
03-30-2012, 02:52 AM
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 :whistle: ), so give me a bit of time.

Hoopsah
03-30-2012, 02:59 AM
Take as much time as you need Bob.

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

Bob Phillips
03-30-2012, 03:29 AM
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
03-30-2012, 04:00 AM
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
03-30-2012, 04:19 AM
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
03-30-2012, 04:52 AM
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
03-30-2012, 06:34 AM
Do you want me to make those changes?

Hoopsah
03-30-2012, 06:45 AM
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
03-30-2012, 07:13 AM
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
04-01-2012, 03:41 PM
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
04-02-2012, 01:14 AM
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
04-02-2012, 01:19 AM
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
04-02-2012, 01:39 AM
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
04-02-2012, 01:52 AM
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 (:doh:), including the code that should have been deleted.

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



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


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
04-02-2012, 02:08 AM
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! :bow:

Bob Phillips
04-02-2012, 02:11 AM
<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?

Hoopsah
04-02-2012, 02:12 AM
I have used them (briefly), hadn't thought about them for this project though.

Bob Phillips
04-02-2012, 02:40 AM
With just one database table now, pivotting it might provide most of the stats that you need.

Hoopsah
04-02-2012, 04:16 AM
Yes, I think your right.

I had a quick play there just looking at the pivots available and your right, it would do the job without me cluttering it up with formulas.

I will try and get it finished of today and tomorrow and let you know

Hoopsah
04-06-2012, 04:37 AM
Hi Bob

Ran in to another wee problem.

You supplied the formula:
=IF(ROWS(U$1:U2)<=SUM(--(COUNTIF(Database!$B$1:$B$500,Database!$B$1:$B$500)=1)),
SMALL(IF(COUNTIF(Database!$B$1:$B$500,Database!$B$1:$B$500)=1, Database!$B$1:$B$500),ROWS(U$1:U2)-1),"")

But, it has now dawned on me that some of the references will have letters as well as numbers (Typically in the format of XX999999)

Do you know how to amend the formula to accept letters too?

Again accept my apolgies for not making this clear first time round - it's like the gift that keeps giving!!

Cheers Bob

Gerry

Bob Phillips
04-06-2012, 06:51 AM
Gerry,

That is the old formula, it changed significantly in #34. Are you using the new on?

Hoopsah
04-12-2012, 12:51 AM
Hi Bob

(Sorry for the delay in getting back to you - Easter illness!!)

Yes, I have checked and this is the same formula from #34 - held in the Workings tab under the Reference header.

Hoopsah
04-13-2012, 06:40 AM
Going on holiday for 1 week

Take Care ;)

Hoopsah
04-23-2012, 03:04 AM
Hi

Back from hols ;(

Still stuck with this one Bob. I have doublechecked that I am using the same formula that you provided in #34 and I am.

Whenever I enter a reference with a non-numeric (Letter) character then it saves it in the list as #NUM!

I can think of a way around this but it would be very longwinded, could you have another wee look at it for me please,

Cheers Bob

Gerry

Hoopsah
04-25-2012, 01:52 AM
Final bump!

I know I keep on Bob, but if you could possibly have a wee look at this one again :help

Bob Phillips
04-25-2012, 01:59 AM
Sorry Gerry, I lost track of this one. I will refamiliarise today and see what I can do.

Hoopsah
04-25-2012, 02:02 AM
Thanks Bob :)

Hoopsah
05-17-2012, 04:07 AM
** BUMP **

Sorry Bob, I don't want to harass you but I am no further with this one.

Still can't get it to accept letters but only numbers and it has now transpired that a lot of our applications will begin with 2 letters.

Can I ask you to have a wee look again - If you need anything further then please let me know,

Cheers Bob

Gerry

Hoopsah
05-22-2012, 06:29 AM
Hi Bob

Still trying to get this to work!!

I am now encounternig another problem.

When I am on the Search tab, I cannot amend the details in column A as I keep getting a message: The Text string you entered is too long

I can't even see what is in there to make it any shorter - can you help?

Cheers Bob

Gerry