copy conditional with change management

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

Yes, please.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

the same story...unable to attach files
so sending you the file by email.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

I don't know why you have problems attaching the files. Here is the latest one.
el3.xlsm
Question:
S0870.png
What should happen if Dx = Solenoids and Ex <> SOL_RELAYS? And if Dx = Control_Valves and Ex <> REGULATED?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

Dear Hans,
About attachments: Many times it has happened. When I hit the button 'Add the file' the name of the file in the 'Browse File" window disappears and the file does not get attached. I even wait for 2-3 mins. But I have been lucky a couple of times.
About "What should happen if Dx = Solenoids and Ex <> SOL_RELAYS? And if Dx = Control_Valves and Ex <> REGULATED?"
Ans: Ok. let me make it more straight forward by removing the AND conditions. So the logic now becomes
"What should happen if Dx = Solenoids ? And if Dx = Control_Valves ?"
Ans:

Solenoids A6:J6 Solenoids A1:AT1 Loop_diag A9:AT9 Hook_Up
Control_Valves A7:J7 Control_Valves A1:AT1 Loop_diag A9:AT9 Hook_Up

Iam sending you a snap shot of what happens when I hit the "Add the file" button. Please advise me if I am missing something over here.
Sincerely, DVM

C:\Users\vilas\Desktop\Instraid\el.xlsm

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

OK, so you mean OR instead of AND. I'll have to look into it, it's complicated. It'll require restructuring the List sheet.

I have no idea why you have problems adding attachments to a post. I have never heard of this before. You might try clearing your browser cache and restarting Windows, but I'm not sure it would solve the problem.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

Thank you, I will try a different browser.
On the application, I know the application is a bit complex. I believe this piece of the work is the last straw. I am struggling.
In fact i have come across some experts who say that what is achieved so far, is by itself somewhat complex. But it was achieved.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

Your latest workbook (attached as el3.xlsm 4 replies up in this thread specifies the source sheet, source range and destination sheet for each copy operation, but not the destination range. For the copy operations that use Ranges_Port as source sheet, the destination range is already indicated in the List sheet of the 'real' workbook. Could you provide the destination ranges for the others (using Ranges_Land as source sheet)? Thanks!
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

I think now that you have understood the application quite well, I think it would be more reasonable to refer to the real workbook.
The destination range for Loop_Diagram would be A5 to T25 and for Hook_Up it would be A51 to T100

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

OK, I think I've got an idea about the structure now. I'll look at the code next.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

Sorry to be back, I'm confused again.

To select Sol_Relays in column E, the user must already have selected Solenoids in column D, so the copy actions for Sol_Relays have already taken place.

There is no way to ever select Regulated in column E, as far as I can tell. So the copy actions for Regulated cannot take place.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

Please refer to the below email sent earlier.

Dear Hans,
About attachments: Many times it has happened. When I hit the button 'Add the file' the name of the file in the 'Browse File" window disappears and the file does not get attached. I even wait for 2-3 mins. But I have been lucky a couple of times.
About "What should happen if Dx = Solenoids and Ex <> SOL_RELAYS? And if Dx = Control_Valves and Ex <> REGULATED?"
Ans: Ok. let me make it more straight forward by removing the AND conditions. So the logic now becomes
"What should happen if Dx = Solenoids ? And if Dx = Control_Valves ?"
Ans:

Solenoids A6:J6 Solenoids A1:AT1 Loop_diag A9:AT9 Hook_Up
Control_Valves A7:J7 Control_Valves A1:AT1 Loop_diag A9:AT9 Hook_Up
for Ex. it is NOT going to be Dx = Control_Valves AND Ex = Regulated. It is only goingto be if, Dx = Control_Valves, then copy......


Iam sending you a snap shot of what happens when I hit the "Add the file" button. Please advise me if I am missing something over here.
Sincerely, DVM


So, I have removed the condition of colD AND colE . The condition only applies to Col D.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

So I can completely ignore the extra conditions on column E? :sigh:
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

That is correct!

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

I'll have to undo some of the changes that I already made to the design of the workbook and the code.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

So sorry about that, but was it a big change? Do you already consider Col E?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

I had already changed part of the code to consider column E too, but then I discovered that the conditions were not relevant.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

See if this version does what you want. It's hard to be certain since I don't really understand the code, so I had to make some guesses.
3d_01.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

Dear Hans.V,

Unfortunately not. More than anything else, I am concerned for not being able to give you an accurate idea of what the code does. Let me try the textual way.

MasterList is my front page. D is the change event col. Its values are selected by drop dpwn lists. E is dependent on D and F is dependent on E. Please ignore all other cols.
The critical part of the code is centered around D.
What operations can be done on D:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PART A: Copy Range from Source to Destination based on selection of Dx values.
- Select a value from drop down.
- Based on this value, a range is copied from source to destination as is mentioned in the wsh 'List'
- D can assume any of the 13 values from the drop down list. So there are 13 destination wsh named exactly the same as the cell values in D.
- for ex. if Dx = Alarms, a range is copied from wsh Ranges_Port to the wsh called Alarms.
similarly, if Dx = Transmitters, a range is copied from the wsh Ranges_port to the wsh called Transmiters.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PART B: 'DELETE' OR MODIFY Dx which was selected earlier.
- The code recognises DELETE function ONLY when the action of DELETE is performed by hitting the hard key 'Delete' on the key board. ALL OTHER FORMS OF CLEARING CELL D OR DELETING CELL D ARE DISABLED IN THE CODE.
(Actually, the contents of cell D could also have been CLEARED by any of the below methods - but none of them could trigger the CHANGE EVENT code for performing CHANGE MGT. -- Right click on cell D and use Clear Button delete button, or Right click on the row and use Delete Row or clear contents button, etc.)
What is CHANGE MANAGEMENT:
The cell D is considered to be an Object. The properties of this object are the ranges copied into the destination sheets. So, if the Object changes (one of the 13 values from the drop down list) its property also changes. There are two events that can happen to cell D - Change its value from the current value and Delete cell D itself.
When the current value is MODIFIED to a new value, the earlier copy is MODIFIED to a new copy.
Similarly, When the current value of D is Deleted (using HArd Key), the copy range that corresponded to this value of D is also deleted.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PART C: The most important part of the code which is generally overlooked in order to understand the complete project, is to consider what happens when MasterList sheet gets populated into hundreds of rows.
Consider population of Col D into sevral rows, say 20 rows. Dx, x=5 to x=24 where x represents row number.

Say D5, D9, D10, D13,D18,D21 = ALARMS, D6,D7,D8,D11,D17,D20 = TRANSMITTERS, D14,D19,D21,D22,D24 = SWITCHES AND D12,15,D16 = CONTROLLERS
In the sheet Alarms, we have copied the same Range 6 times. Let us name these ranges as
R1(A), R2(A), R3(A), R4(A), R5(A), R6(A) corresponding to Dx = D5, D9, D10, D13,D18,D21 RESPECTIVELY. Similarly,
D6,D7,D8,D11,D17,D20 RESPECTIVELY CORRESPONDS TO RANGES R1(T), R2(T),R3(T),R4(T),R5(T) AND R6(T) Copied in wsh 'transmitters'
D14,D19,D21,D22,D24 RESPECTIVELY correspond to Ranges R1(S), R2(S). R3(S), R4(s) and R5(S) copied in wsh 'Switches' and finally,
D12,15,D16 RESPECTIVELY correspond to Ranges R1(C), R2(C,) R3(C) copied in wsh 'Controllers'


Let us now consider the operations DELETE on any cells D5 to D24

If D17 is deleted, then R5(A) is deleted, R6(A) occupies the position of R5(A) and R6(A) then becomes R5(A)
So, wsh Alrams now has 5 ranges R1(A), R2(A), R3(A), R4(A), R5(A),

If D12 is deleted, R1(C) is deleted, R2(C) and R3(C) move up by one Range and then R2(C) becomes R1(C) and R3(C) becomes R2(C)
So, wsh Controllers now has two ranges R1(C) and R2(C)


Let us now consider the operations MODIFY on any cells D5 to D24
If D17 is modified to a new value D17= Switches, the two actions are performed
Action 1: R5(A) is first deleted as mentioned above, R6(A) occupies the position of R5(A) and R6(A) then becomes R5(A)
Action 2: A new Range is inserted in wsh Switches.
Question is where is it inserted? Right at the top or Right at the bottom? The Answer is: None of these two places, but it is inserted IN BETWEEN the current Ranges R1(S) and R2(S) in the wsh 'Switches" So, in effect, what happens in wsh 'Switches' is all ranges R2(S), R3(S), R4(S) and R5(S) are pushed one range below their current positions and the new copy is inserted below R1(S) and R2(S). Therefore the old R2(S) now becomes R3(S) and same with Old R3(S), R4(S) and R5(S).

Question: Why does the copy happen this way?
Answer: Because the modified range D17 lies between D4 and D19 in the sheet 'Switches'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I hope this clarifies the situation.
If this was already understood and is un realistic to make the changes I requested, then I would be feeling if not you guys, then some one else also may not be able to do it. In that case I will have to make a compramise in my requirements and request you to try that option which I guess would be quite easier.

Please advise me.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: copy conditional with change management

Post by HansV »

Sorry, if the workbook that I attached doesn't do what you want, I'm afraid I can't help you.
Best wishes,
Hans

DVM
2StarLounger
Posts: 110
Joined: 19 Jul 2014, 08:26

Re: copy conditional with change management

Post by DVM »

I respect your thoughts and cant wait to thank you so much for your help.
I wish I can comeup with realistic expectations next time around.
Best regards, DVM