Insert With Worksheet_Change (VBA)

[Nick72310]

Hello,

The basis of what I am trying to do is make a list with populating data. Exactly what Vlookup does but my range of rows can change from 1 to 20. Vlookup can be a problem when the number of rows is a variable larger than 1. So I have tried to use insert instead. Copying the data from the table and inserting the copied cells in my list.

I had my spreadsheet set up where I make a change to a cell in column B and press enter, and then have it insert a range of copied cells at that active cell where the code was entered. I can successfully do that, however, it's a never ending loop and repeatedly inserts the copied cells.
So I thought maybe it was looping because after the insert, there was another change to the worksheet and repeated the loop. So what I tried to do was use a targeted range in column A (a number that also represents the number of rows being inserted) but I couldn’t figure it out.

My code can be seen below. If you have any questions of what I am trying to accomplish, just ask.
Also to note: Although not a priority at this point, I also want the code to do the opposite. If I delete a cell value in column B, I want it to delete the rows that were inserted with it. This is where I saw the represented number in column A coming into play.

Thank you in advance, I appreciate the help!



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A5:A100").Address Then
Set PrevCell = ActiveCell
Range("B3:N3").Select
Selection.Copy
PrevCell.Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
End Sub

[mancubus]

welcome to VBAX.
please use code tags when posting your code here.

clicking # button will insert tags automatically.

like:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:A100")) Is Nothing Then
Application.EnableEvents = False
Range("B3:N3").Copy
Target.Insert Shift:=xlDown
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub

[Aflatoon]

Vlookup can be a problem when the number of rows is a variable larger than 1.

Can you expand on this statement? It makes no sense to me.

[Nick72310]

I am trying to lookup 1 value from a table, and with that value, I want it's corresponding information. That information entails a number of different rows depending on the value I am looking up. Vlookup only populates one row of information for me.

[Aflatoon]

I see. You want MATCH then - that will return the position of the lookup value within the table and you can use that in conjunction with INDEX to return data from wherever you want based on that position.

[Nick72310]

I should also mention that I have two tables. The first table contains all the data that I need. The second one is a table of formulas with all the different combinations of rows.

To better explain, I have attached an image if the formula table. As you can see from the image, the number of sequences (Seq) can change for my code and column A represents the number of rows. In these cells, the formulas are various Vlookup formulas. Everything in these tables work fine. I just need to figure out how to insert these copied cells into my list with the corresponding number of row. (The image shown has three rows)

14697

[Nick72310]

I see. You want MATCH then - that will return the position of the lookup value within the table and you can use that in conjunction with INDEX to return data from wherever you want based on that position.

I have tried this but as I said, the number of rows changes and I don't know which value the user will want to look up. I have no issue using match and index except for when the code in the cells is set up for something with 4 rows and this value only needs 2. Then I have a bunch of blank spaces. Make sense? And maybe we are talking about different things, I don't know. I would need more clarification if we are.

[Aflatoon]

I had no idea what your layout was until just now... ;)

[Nick72310]

Any suggestions?

[Nick72310]

Update:
I have successfully coded it to where it is not an endless loop! Now the goal is to write a statement that selects how many rows it needs (this value is in Column A). I will have to figure out how I am pulling that info for how many rows it needs but I still need help with writing the code for the different possibilities. I would image 1 to 15 rows would be the range.
I also need help on extending my range to work in all cells for column C. Not just cell C18.

Below shows images of my outcome so far, as well as the code I've used to get it.

I have in the first image, the number 10 in cell B18 and the code (TTXC) which all my Vlookup formulas are based off of. In image 2, you can see that I have witen code to move the number 10 down a row compared to the code (TTXC). I would like to do the same with the code (TTXC), and move it down but I can't figure it out. I also have the option of moving all the other cells up to row 18 along with the code TTXC. I have some code below labeled as comments ('Green Text) so you can see what I was trying.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("C18").Address Then
Set PrevCell = ActiveCell.Offset(0, -2)
Range("A3:N3").Select
Selection.Copy
PrevCell.Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False


Set NextCell1 = ActiveCell.Offset(-1, 1)
NextCell1.Select
Selection.Cut Destination:=ActiveCell.Offset(1, 0)

'Set NextCell2 = ActiveCell.Offset(0, 1)
'NextCell2.Select
'ActiveCell.Cut Destination:=ActiveCell.Offset(1, 0)
'Range("A1").Select

End If
Exit Sub
End Sub

14706
--------------------------------
14707

[Nick72310]

Update 2:
I have successfully managed to write an if statement for the number of rows that I need inserted. I have also figured out all the formatting issues (ie. Moving the user input to the same row as the insert).

Now I have to figure out how to extend the range so it's not just when one specific cell changes, but for when an entire row changes. I have no clue how to do this, so please someone help...
And then the final step would be code the deletion step. I want to be able to delete the inserted rows (variable number based on Row A, see image)

My code is shown below, along with an image for my format thus far.
p.s. I only show my code for 1 row. 2 or more rows is exactly the same but with: If Range("B18").Value = 2 Then


Private Sub Worksheet_Change(ByVal Target As Range)


If Range("B18").Value = 1 Then
'On Error Resume Next
If Target.Address = Range("D18").Address Then

Set CodeCell = ActiveCell.Offset(0, -2)
Set NextCell = ActiveCell.Offset(-1, -1)
Set PrevCell = ActiveCell.Offset(-1, 0)
Set Movecell = ActiveCell.Offset(-1, -2)

Range("B3:O3").Select
Selection.Copy
CodeCell.Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

Set HereCell = ActiveCell.Offset(-1, 0)
HereCell.Select
Selection.Cut Destination:=HereCell.Offset(1, -1)

NextCell.Select
Selection.Cut Destination:=ActiveCell.Offset(1, 0)

PrevCell.Select
Set PasteCell = ActiveCell.Offset(1, 0)
Selection.Copy
PasteCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Movecell.Offset(0, 1).ClearContents
HereCell.Offset(-1, 3).ClearContents
End If
End If
Exit Sub
End Sub

14710

[p45cal]

Supply a file; if it's sensitive, desensitise it with some careful (not wholesale) search and replace, remove irrelevant sheets.
This is probably much more straightforward than you're making it but those wishing to help need something to work on rather than their guessing (wrongly) what you have and then you getting duff advice.

[Nick72310]

Supply a file; if it's sensitive, desensitise it with some careful (not wholesale) search and replace, remove irrelevant sheets.
This is probably much more straightforward than you're making it but those wishing to help need something to work on rather than their guessing (wrongly) what you have and then you getting duff advice.

Other than the table with all my data (which is sensitive information), I have provided everything that is present in my excel file.

[Nick72310]

But if it will help, I managed to find to time to create an example file.
14729

So to reiterate what I am trying to do...
I want to be able to auto populate data based on the user input of the "Code" to be able to generate a list. This data can have a variable number of rows (up to 15?) for each code (see table in file). My idea was to copy and insert rows when the Code was defined. These rows would come from a "table" that already has the necessary formulas in it and the different combinations for codes & rows (see file if not clear description). This is working in my Macro code but it only works for one predefined cell in my Macro code. I need this to work for a list. Meaning, If I define the code in any cell from column D, I want the rows to be inserted at that location (using a range would be preferred. ie. D15:50).
If the user decides to delete the "Code" they defined, I would like to see those rows be deleted (the ones that were originally inserted). In my file, I have thought about using Columns A & B to do this. This keeps track of how many cells were inserted.

Also, note that I want the user to be able to manually change anything in the data inserted from the code.

I hope this is clear enough and makes it easier for people to help. You are more then welcome to take a completely different approach on how to accomplish this but it needs to have the same functionally as described above. And the worksheet will be in file that has a lot more worksheets with Macro codes so there may be other "guidelines" it needs to follow.

Thank you for your help!

[p45cal]

Does the table in the vicinity of A19 in the attached even remotely address the functionality you're looking for?

[Nick72310]

It's not quite what I am looking for. This won't be something I want to filter because it will be a routing for a manufacturing process. So it will be a list of steps that needs to be done. The order will matter and being able to see all of it is important as well. So when the user adds something to this list, they will be not be adding info to the table, only to the "list". I hope this makes sense.

I have provided a completed "routing" with the format I would like to see in the file below.
14734

p.s. That filter box is pretty cool though, I've never seen that.

[p45cal]

1. If the user enters a given code, will you always want to see all rows with that code from Table1 in the list or sometimes fewer? - I'm also trying avoid having 2 source tables, and only use Table1.
2. Does the produced list have to have formulae in, or can it be plain values?

I'll be on and off looking at this, so be patient.

[Nick72310]

1. If the user enters a given code, will you always want to see all rows with that code from Table1 in the list or sometimes fewer? - I'm also trying avoid having 2 source tables, and only use Table1.
2. Does the produced list have to have formulae in, or can it be plain values?

I'll be on and off looking at this, so be patient.

1. There will be other data in the table that is not relevant to this list and will not want to be seen in the list. But I will always want to see all the rows, not necessarily all the columns. Noting that I do not want to see the Code, sequence and a couple other columns in duplicate lines. This is because I can't have people thinking they need to do that step again. Even if it has a the same sequence number, someone will overlook that and do the step again. I can't risk that. (See image for an example that I do and do not want to see)

2. It can be values... I just used formulas because then I always know I'm getting the correct data.

14738

[p45cal]

see attached; work in progress, proof of concept, whatever.

[Nick72310]

see attached; work in progress, proof of concept, whatever.

Honestly... This is one of the coolest things I've ever seen!!! It's exactly what I need!

Just a few minor touches...
- I like the empty row between each input, but I don't need the header columns every time. It will be sufficient enough if I just have the headers there to before they input anything. I would also like to fill that empty row (from cells columns A:I) with a colored cells if possible (not a big concern).

- There will be times when there is no code in the "master table" and everything will be manual entry. Is there anyway I can get a blank row put in with borders around the cell, without having this in the "master table"? I would imagine only one row would be necessary but if the user could select how many, that would be useful. Also enable auto uppercase letters when they do manual entries. This would include anytime they modify a cell in the list. (This could be related to the next suggestion, a reorder may be all that's needed.)

- I like the Renumber Sequence, but I'd also like to see a Reorder sequence. So if the user forgets a line, they don't have to delete others rows to fit it in between.

- A cool feature would also be to auto highlight a cell when any manual entry occurs.

- I would like each row to have "All Borders", expect for the cells with null information (ie. Columns B, C and 8 when multiple rows are present). The other option is to have "Outside Borders" for the entire input. I see it's taking the format from the table, so I don't know if this is possible. I think I would prefer the Outside border though.

- Cell C15, where the code is entered, you have that as a drop down list. My master table is 10's of thousands of lines long so a drop down is not necessary. I would be like to see an autocomplete feature in that cell. I have never been able to figure that out in a macro. I'm sure you know what I am talking about, but just to be clear, if I have the code TTXC in my master list, and I start typing the letters TT. I would like to see the options of all the codes that start with the letters TT. (Ps. the master table in actually in a different worksheet, but same workbook).


I see you used name manager & macros to accomplish this. Are there any other pieces I need when I transfer this to my actual workbook? I know I will have to adjust the names in name manager and the cell ranges.

I really do appreciate your help! And all my suggestions are more for aesthetics. The functionality of your code is perfect and exactly what I am looking for. So if you can add some of the suggestion with no problem, that would be great. If not, I won't loose sleep over it. :)

You are more than welcome to put your name in the code to if you want recognition. Either real name or user name.

[p45cal]

Just a few minor touches...
- I like the empty row between each input, but I don't need the header columns every time. It will be sufficient enough if I just have the headers there to before they input anything. I would also like to fill that empty row (from cells columns A:I) with a colored cells if possible (not a big concern).I've had a stab at this in the attached.




- There will be times when there is no code in the "master table" and everything will be manual entry. Is there anyway I can get a blank row put in with borders around the cell, without having this in the "master table"?Done.




I would imagine only one row would be necessary but if the user could select how many, that would be useful.Done.




Also enable auto uppercase letters when they do manual entries. This would include anytime they modify a cell in the list. (This could be related to the next suggestion, a reorder may be all that's needed.)Later.




- I like the Renumber Sequence, but I'd also like to see a Reorder sequence. So if the user forgets a line, they don't have to delete others rows to fit it in between.No change required, if a user forgets a line, he enters any sequence number into cell B15 between the sequence numbers above and below where he wants to insert. If there is no whole number available between the lines he wants to insert he can resort to decimals. All adds are actually inserts. Or he could renumber at any time of course. ReOrdering would be convoluted, coding-wise.




- A cool feature would also be to auto highlight a cell when any manual entry occurs.Possible, what kind of highlighting?




- I would like each row to have "All Borders", expect for the cells with null information (ie. Columns B, C and 8 when multiple rows are present). The other option is to have "Outside Borders" for the entire input. I see it's taking the format from the table, so I don't know if this is possible. I think I would prefer the Outside border though.I think I've done the latter.




- Cell C15, where the code is entered, you have that as a drop down list. My master table is 10's of thousands of lines long so a drop down is not necessary. I would be like to see an autocomplete feature in that cell. I have never been able to figure that out in a macro. I'm sure you know what I am talking about, but just to be clear, if I have the code TTXC in my master list, and I start typing the letters TT. I would like to see the options of all the codes that start with the letters TT.I'll have a look and see how easy it might be.




(Ps. the master table in actually in a different worksheet, but same workbook).I realise; as said: proof of concept, work in progress.




I see you used name manager & macros to accomplish this.Macros yes, Name Manager no; the code creates/adjusts the defined Names.




Are there any other pieces I need when I transfer this to my actual workbook?Loads.




I know I will have to adjust the names in name manager and the cell ranges.Maybe, maybe not.




You are more than welcome to put your name in the code to if you want recognition. Either real name or user name.You can do that if you want, just use p45cal and refer to vbaExpress.com (I'm not affiliated to vbaExpress.com at all).

The code relies, in many places, on CurrentRegion (the same as when you press F5, choose Special…, Current Region and OK on a sheet) so completely blank rows (colouring doesn't matter) between entries is crucial to the proper working of the code. Put something in those blank rows, or delete them altogether and it will foul up. Conversely, if someone deletes an entire column within a sequence entry, the code is likely to foul up too. Until you wanted no headers this wouldn't have been a problem since the headers retained that horizontal continuity, but now you need to be aware of it (I suppose it could be coded around later). So you might notice that the blank sequence entries cells are not actually blank - they each have a single space in them.

[Nick72310]

I like the empty row between each input, but I don't need the header columns every time. It will be sufficient enough if I just have the headers there to before they input anything. I would also like to fill that empty row (from cells columns A:I) with a colored cells if possible (not a big concern).

- Can the headers just stay static in row 17?

A cool feature would also be to auto highlight a cell when any manual entry occurs.

- Just something like filling the cells in yellow?

[p45cal]

I like the empty row between each input, but I don't need the header columns every time. It will be sufficient enough if I just have the headers there to before they input anything. I would also like to fill that empty row (from cells columns A:I) with a colored cells if possible (not a big concern).

- Can the headers just stay static in row 17?I've done both of these in my last file!? Except it's row 18, and they're only added once with the first addition.


A cool feature would also be to auto highlight a cell when any manual entry occurs.

- Just something like filling the cells in yellow?Yellow.. OK, but not today.


The danger with leaving them static is that someone will change them. (Now I s'pose I could allow them to be changed, get the code to check they're valid headers and there are no gaps, then get the code to output in the same order - but not today).

[Nick72310]

I've done both of these in my last file!? Except it's row 18, and they're only added once with the first addition.

Yellow.. OK, but not today.


The danger with leaving them static is that someone will change them. (Now I s'pose I could allow them to be changed, get the code to check they're valid headers and there are no gaps, then get the code to output in the same order - but not today).


I know you addressed both the items I mentioned in the first statement. I just copied and pasted the sentence I was referring too. I suppose all I really needed was the header part of the comment.

I intend to protect certain cells of the worksheet/workbook, so the header could be locked where no modifications can occur. Also, the document will always be opened as a "fresh" document for the user to use. So they will never overwrite the file with their current data. If for some reason the macro was disabled or not working correctly, the problem would never be an on going issue.

[Nick72310]

I hope this doesn't make it more difficult, but this will be the final header format of the table. It has merged cells (D:E, I:J, M:O). See image below

14792

[p45cal]

I hope this doesn't make it more difficultOh yes it does; any seasoned vba coder will tell you merged cells and vba don't go together - the resultant code would be flaky and would need lots of IF statements to try to handle merged ranges properly! Excel supports columns of any width. Use them.
Regarding your "My master table is 10's of thousands of lines long so a drop down is not necessary. I would be like to see an autocomplete feature in that cell."
I've been exploring possibilities, the main problem being tolerable speed of updating the reducing list of options whilst retaining user-friendliness. I've found an acceptable solution but I'm still tweaking it; I'll put together a proof-of-concept on this soon, but warn you that it won't be 'dead easy' to port it over to your project. I had a solution which was OK for a starting list 500 entries but it was intolerably slow to update starting from a list of '10s of thousands'.

[Nick72310]

14799

Attached is the file I plan to use. I just finished setting this up. If you want to use it you can. My data tables are not included so you may need to just transfer the one you've been using into it.

[p45cal]

I'm confused; you said
Cell C15, where the code is entered, you have that as a drop down list.C15 had a dropdown of codes from the table above in rows 2 to 7. You also said
My master table is 10's of thousands of lines long from which I inferred that you would have your master table somewhere else, otherwise it would encroach on your:
Can the headers just stay static in row 17?Now I see on your latest sheet that you still have the headers (albeit on row 41) and a small master table (empty) of some 20 rows on the same sheet. Where could the master list of 10s of thousands fit?

[Nick72310]

I can see where the confusion is coming from...

In may latest excel file, I have a table from B8:M28. That is note master table. My master table will be in a different worksheet (see image). I left the table from B8:M28 in the file so you could see my exact format within the spreadsheet. And you could see why I had merged cells.
This file is going to contain a lot more info then what you are helping me with.

Did this clear up the confusion?

14811

[p45cal]

Regarding "My master table is 10's of thousands of lines long so a drop down is not necessary. I would be like to see an autocomplete feature in that cell."
I've been exploring possibilities, the main problem being tolerable speed of updating the reducing list of options whilst retaining user-friendliness. I've found an acceptable solution but I'm still tweaking it; I'll put together a proof-of-concept on this soon, but warn you that it won't be 'dead easy' to port it over to your project. I had a solution which was OK for a starting list 500 entries but it was intolerably slow to update starting from a list of '10s of thousands'.The atttached is only a proof-of-concept for a tolerably speedy autocomplete with a largish list (~60k). The list of (unsorted) words is on a separate sheet, this list gets copied and sorted every time that sheet is de-activated. The other sheet has cell E15 as a 'target' cell which you can select or double-click. See what pops up when you do this; use the down-arrow key to move to the list, Enter or double-click to select. Esc to abandon. You don't have to choose one of the words, you can edit the word in the upper box, or make it blank and it will be accepted.
At the moment, I'm working on a tweak where once you're in the list of words, you can still edit the upper box (at the moment it's not very friendly when you add/delete a letter).

Tried attaching but it's too big for this site (list of words) so linked-to here: https://app.box.com/s/vi66t7qm0okncwkueh8nj2l6n8x0dvhd

[Nick72310]

Sorry for the delayed response... The autocomplete looks great so far! That will be very useful.

[Nick72310]

Any progress on the other stuff, other than the autocomplete list?

[p45cal]

Any progress on the other stuff, other than the autocomplete list?Remove all merged cells from the table and the destination ranges, move the table to where it's going to be (move the destination too if necessary), put something half reasonable in the table so that I'm not moving blank cells around (helps when debugging to check the right stuff is going in the right places).
Essentially, bend over backwards to make it as easy as you can for us to help you!

[Nick72310]

I have attached the document I plan to use. I have the table in their with all the codes. I just erased all the confidential info. I just named the table [Table]. Other than the codes, I left the table blank, I know you wanted me to fill it with something but I figured I would let you decide what would be ideal to help code. The table is setup to use though. I tried converting your old code into my worksheet but did not figure it all out. As far as the merged cells go, I'm okay with you just ignoring them as merged cells. Just leave the cells black where I have a merge. You can see what I mean if you view the attached file.

Thank you.

14869

[Nick72310]

You know what, I'm going to send you a more complete file to make it easier...

[Nick72310]

Attached is a file that has much more detail to my problem. I know I've asked a lot from you so far so I really do appreciate your help! If you have questions, let me know.

Also, as I have mentioned previously, it would be a nice feature if when the user made a manual change, it would automatically highlight the cell. This is something I would want to define a range for (not the entire worksheet). And thinking ahead, if they delete their manual change, the yellow highlight would be gone too.

14882

[p45cal]

1. Which of these:
STANDARD_OPERATION_CODE,STANDARD_OPERATION_DESCRIPTION,DEPARTMENT,DESCRIPTI ON,RESOURCE_SEQ_NUM,RESOURCE_CODE,BASIS_ITEM USAGE_RATE,LOAD_DATE,ID

pair up with:
Code,Department,Description,Sub-Seq,Resource,Basis,Usage,Operation Notes,Labor Rate ($),OVH Rate ($),Pending ($)

2. Merged cells are really being a pain in trying to code this; I can't just ignore them (currentregion, copying values, pasting values are just some of the things snarled up by merged cells). I will continue to try and circumvent them but the code will be flaky and is almost guaranteed to fall over at some point in the future (I can't think/predict all merged cell behavious in vba!). It really would be much better all round (especially to you) to be rid of them. Would you?

[Nick72310]

14909

I have created a small tables corresponding the headers for the tables.

Just doubling checking that you noticed in the last file, I did technically remove the merged cells. Those few cells just have a blank cell next to them now to allow for extended space... If that is still an issue, I did remove one of the "merged" cells. I would really like to keep all of them to be honest, otherwise the format looks goofy because of the size of some words in the tables. Is there one in particular that is causing a problem or just all of them (cells: DE, IJ, M:O)?


1. Which of these:
STANDARD_OPERATION_CODE,STANDARD_OPERATION_DESCRIPTION,DEPARTMENT,DESCRIPTI ON,RESOURCE_SEQ_NUM,RESOURCE_CODE,BASIS_ITEM USAGE_RATE,LOAD_DATE,ID

pair up with:
Code,Department,Description,Sub-Seq,Resource,Basis,Usage,Operation Notes,Labor Rate ($),OVH Rate ($),Pending ($)

2. Merged cells are really being a pain in trying to code this; I can't just ignore them (currentregion, copying values, pasting values are just some of the things snarled up by merged cells). I will continue to try and circumvent them but the code will be flaky and is almost guaranteed to fall over at some point in the future (I can't think/predict all merged cell behavious in vba!). It really would be much better all round (especially to you) to be rid of them. Would you?

[p45cal]

Ridiculous amounts of time spent coping with merged cells in the attached.
Could you check the formula you want putting in column Q; it looks weird that you add $ amounts to Usage values.
Formulae in columns P and Q currently will only work if there is only one unique line in OracleResources2 that matches the 2 criteria. If there's more than one they'll be summed.
Cell J37 temporarily needs to stay as it is.

[Nick72310]

The amount of work you have put into this truly shows! Your code works better than I ever expected :bow:. I appreciate every minute you have contributed to helping me and it's nice to see it's all coming together.

The formula in column Q is correct. What's happening is that the two rates (labor and overhead) are being added together and then being multiplied by usage (time) for each operation. Thank you for double checking.


Formulae in columns P and Q currently will only work if there is only one unique line in OracleResources2 that matches the 2 criteria. If there's more than one they'll be summed.


I am working to filter my master tables so this does not occur. I don't want to have multiple items with those two criteria. So in the future this should not be a problem. So your code now should be just fine for this.

To answer my previous question where I wanted to add the rows for my pending cost. I have kind of come up with a solution. But kind of not because it opens the door to another question but this is what I was thinking (see images below).

I thought I could leave row 44 static with the summation formula so when lines are inserted in between, and the formula would still work. And it does... The only issue with this is the "Clear All" macro. It deletes everything below Row 44. I thought setting the limit from row 44 to the value of cell I45 (number of rows added)?

For the first image, the Formula in cell I45 is actually ROWS(I42:I44)-3

14946

14945


One thing I noticed, and maybe it is because of the duplicate resources with the same two criteria, but when you add a code to the routing and then manually change the resource number, the formulas in columns P and Q do not work. They just display zero.

I also tried testing all the code with it being a protected worksheet but it didn't work. It wouldn't insert cells, even if all the "features" are selected. I have included an image to where it errors out in the "add" macro.

14943

14944

One last thing I thought of was, what if a Standard Operation Code was deleted from the master table? Would the list on the "Hide" worksheet delete it as well? This really is NOT that important, because it is an unlikely scenario. I was more just curious what the table would do if this case really did happen.

[Nick72310]

Is there a way to set the inserted columns to have the currency format of dollars, along with 3 decimal places?

Also, What about an outside border around the inserted color row?

[Nick72310]

The most important thing is getting the formula to work. Then the code is usable. The other stuff is more aesthetics.

I did figure out the protection problem, kind of. I need the cells unlocked when they are inserted. I tried to set the cells as unlocked on the "Hide" worksheet, but they don't stay that way. So I tried to add "Destn.Locked = False" in the code but it didn't work for me.

[p45cal]

I'm sorry, this has somehow slipped right by me - I thought the ball was still in your court.
I'll get round to it but it's not trivial, so be patient.

[p45cal]

If you've made significant changes to layout etc. can you repost your file (or PM me for a private email address to the send the file to if the data is sensitive (and you don't mind just me seeing it)).

[p45cal]

In the file VBA Help 6 - Thank You!.xlsm in msg#38, in Q44 you have the formula:
=(O44*(K44+P44))+(O45*(K45+P45))+(O46*(K46+P46))
which translates to:
=($37.60*(23hrs+$376))+($37.60*(24hrs+$376))+($40*(25hrs+$400))
surely this can't be right?
It results in a value of some $47k.
I've put hrs for hours as it shows thos parts of the formula representing time (even if they may not be hours), and $ symbols for money.

This is how I interpret:
the two rates (labor and overhead) are being added together [($37.60+$376)] and then being multiplied by usage (time) [*23hrs] for each operation
=(O44+P44)*K44+(O45+P45)*K45+(P46+O46)*K46
which translates to
=($37.60+$376)*23hrs+($37.60+$376)*24hrs+($400+$40)*25hrs
or more clearly, for the 3 rows:
=($37.60+$376)*23hrs
+($37.60+$376)*24hrs
+($400+$40)*25hrs
This results in about $30k.
if this is right, a shorter formula is:
=SUMPRODUCT(O44:O46+P44:P46,K44:K46)
which will be relatively easy to implement in code.

Am I going bats?

[p45cal]

Regarding the sheet protection, vba has a way of getting round that easily with one of the .Protect's arguments (UserInterfaceOnly) set to True, however, I think I will need to know how you normally protect the sheet (which tick boxes are ticked and whether a password is used). The easiest way to do this is to record yourself a macro protecting the sheet with all the options ticked or not as you wish to be, then sending me the one line of code it generates. You will also need to tell me which cells you want the user to be able to edit, and those not, among the cells that are added when he clicks the add button.

[p45cal]

One thing I noticed, and maybe it is because of the duplicate resources with the same two criteria, but when you add a code to the routing and then manually change the resource number, the formulas in columns P and Q do not work. They just display zero.You can see that the formulae in columns O & P are dependent on values in columns F and I. Both these value must in turn exist on the same row of OracleResources (in columns A and C) and the values in columns F & H therein should not be blank or zero except by intention (zero rates).
Changing the values in column F and/or I for values which exist on a single line in OracleResources should result in valid values in O and P (along with attendant correct values in column Q).

[p45cal]

re:
To answer my previous question where I wanted to add the rows for my pending cost. I have kind of come up with a solution. But kind of not because it opens the door to another question but this is what I was thinking (see images below).

I thought I could leave row 44 static with the summation formula so when lines are inserted in between, and the formula would still work. And it does... The only issue with this is the "Clear All" macro. It deletes everything below Row 44. I thought setting the limit from row 44 to the value of cell I45 (number of rows added)?

For the first image, the Formula in cell I45 is actually ROWS(I42:I44)-3I think we could get the macros to update the values/formulae directly in cells O14,O19,O24 (and other cells in that vicinity if you need them), rather than have some values in mobile cells at the bottom of the list of sequences.

To that end, regarding your ROWS(I42:I44)-3 formula; what are you hoping to show in that cell?, the number of resources?, this might be done with the number of none blank cells in that column, but maybe you want a count of unique resources?

[Nick72310]

Somehow I overlooked the actual formula I wrote in the file. The description I wrote is correct and I have now updated the file.
"The two rates (labor and overhead) are being added together [($0.015+$1.517)] and then being multiplied by usage (time) [*.01375hrs]. Then it is a sum of each line."

Keep in mind that the value for cost are made up in the file. That is why is dollar amount was so large. In the latest file, I made the values a bit more realistic by moving the decimal.

15015

The file was too big to attach so I had to delete some info, but nothing that you should really need.

I successfully managed to work around the protection issues, however the code ran pretty slow then, so that’s when I tried to unlock the inserted cells, so I wouldn’t have to worry about it. The code I used was:


ActiveSheet.Unprotect Password:="IE"
‘Code here
ActiveSheet.Protect Password:="IE", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
userInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=True, _
AllowSorting:=False, _
AllowFiltering:=False, _
AllowUsingPivotTables:=False

All the cells are in the state I want them to be. Meaning if they are locked or unlocked. That’s how I want them. Also, note the cells that have “hidden” checked as well. If you need more defined ranges I can give them to you, but they are kind of sporadic.


You can see that the formulae in columns O & P are dependent on values in columns F and I. Both these value must in turn exist on the same row of OracleResources (in columns A and C) and the values in columns F & H therein should not be blank or zero except by intention (zero rates).
Changing the values in column F and/or I for values which exist on a single line in OracleResources should result in valid values in O and P (along with attendant correct values in column Q).

Try using the STANDARD_OPERATION_CODE, “mach”. Then change the resource to any of the number is department 3151. Try changing the value in column I to 315106023. You will see the values are zero even though the combination does exist in the table. Or you can even try this, select the resource value like you are going to edit it in the formula bar, then press enter without changing anything and see what happens.

I hope this all makes sense! I can sense that we are almost complete! :rofl: Thank you!

[p45cal]

Somehow I overlooked the actual formula I wrote in the file. The description I wrote is correct and I have now updated the file.
"The two rates (labor and overhead) are being added together [($0.015+$1.517)] and then being multiplied by usage (time) [*.01375hrs]. Then it is a sum of each line."
Check the attached gives the right results.





I successfully managed to work around the protection issues, however the code ran pretty slow then, so that’s when I tried to unlock the inserted cells, so I wouldn’t have to worry about it.
All the cells are in the state I want them to be. Meaning if they are locked or unlocked. That’s how I want them. Also, note the cells that have “hidden” checked as well. If you need more defined ranges I can give them to you, but they are kind of sporadic.
I moved the .Protect code which has the UserinterfaceOnly argument set to True to the AutoOpen event. This only needs setting once per file opening; it lasts as long as the file is not closed. The sheet is protected only from the user, not the code.





Try using the STANDARD_OPERATION_CODE, “mach”. Then change the resource to any of the number is department 3151. Try changing the value in column I to 315106023. You will see the values are zero even though the combination does exist in the table. Or you can even try this, select the resource value like you are going to edit it in the formula bar, then press enter without changing anything and see what happens.This was happening becuse the cells were originally numbers as text but the format of the cell was 'General', press Enter after editing such a value and Excel tries to be helpful, recognises a number and treats it as a number, so MATCH failed. I have now formatted those cells as TEXT so you should be able to edit them.

I suspect you'll want other cells to be unprotected - you'll have to tell me which.
You'll probably need to rethink your updating of the cells I38:I39 (try removing a number in the middle of the exisiting sequence numbers and look at what happens to the next default Add sequence number (the same as the last existing one)).

[Nick72310]

Everything looks great!!! The formula and worksheet protection work well.
I would actually like all the cells from C42:Qinfinity to be unprotected, because everything there should be able to be manual. How can I adjust this in the future if needed?
Also, try to add the code QA. It should only be 2 lines. Is there anyway to use an exact match for the code?

One last random thing, do you know why my filter on my Oracle Standard Ops table always disappears from the header? It does not stay on the table.

I am very excited that people at my work can use this now! Thank you!

[p45cal]

1. Attached has columns C:Q for each added sequence unlocked (not the tinted rows between).
2. I don't know what you mean at all by 'Also, try to add the code QA'
3. Re:" do you know why my filter on my Oracle Standard Ops table always disappears from the header? It does not stay on the table."
When an Advanced filter is applied to a range, it clears any autofilter, removes the autofilter arrows too. I don't think it should do this. I have added a line to bring back the autofilter arrows, but not any filter criteria previously applied. Is it important that you keep an existing filter? I did once restore filter criteria for a customer when this happened, but it was convoluted.

An advanced filter is applied twice to that table when adding a sequence, one line is:
Sheets("Oracle Standard Ops").Range("A1:H1419").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("J37:J38"), CopyToRange:=Sheets("Hide").Range("F1:M1"), Unique:=False
the other:
Sheets("Oracle Standard Ops").Range("A1").CurrentRegion.Columns(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Sheets("Hide").Range("A1"), Unique:=True
Neither of these lines uses a reference to the Table you have on that sheet, only a hard-coded range or 'CurrentRegion'. It might be safer to remove the hard code and use the first listobject object (a table) on that sheet:
Sheets("Oracle Standard Ops").ListObjects(1).Range.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("J37:J38"), CopyToRange:=Sheets("Hide").Range("F1:M1"), Unique:=False
and
Sheets("Oracle Standard Ops").ListObjects(1).Range.Columns(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Sheets("Hide").Range("A1"), Unique:=True

This will only work reliably if the table is the first or only table on that sheet. I have changed these lines in the attached.

[Nick72310]

See image... I meant add the code QA with the add button and see what the result ends up being below.
It should only be 2 lines but ends up being 20 because the "QA" is used in other Standard Op Codes. Is there anyway to use an exact match for the code?

15088

[p45cal]

Exact match addressed in attached. Test it!
Also Data validation change in cell J38 to avoid duplicates.

[Nick72310]

I'm not sure what changed with your Data Validation modification in cell J38... But Everything is Working great!!! I just have one last question, how did you modify the cells that I wanted unlocked? If I add more to the workbook in the future, how can I adjust that?

[p45cal]

I'm not sure what changed with your Data Validation modification in cell J38... But Everything is Working great!!! I just have one last question, how did you modify the cells that I wanted unlocked? If I add more to the workbook in the future, how can I adjust that?look for .Locked = False in sub Add(). This unlocks the cells on the Hide sheet before the cells are copied to (inserted in) BOM & Routing (IE)

[Nick72310]

Thank you p45cal! You have helped me more than you can imagine! I truly do appreciate all the time you put into this. I can now mark this thread as... SOLVED! :rotlaugh: