Trouble with combining 2 macros to loop properly

A few problems I'm having. I'm very new to VB and this code so far was put together with help from you guys! This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).

And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.

Please Login or Register  to view this content.

Module:

Please Login or Register  to view this content.

jman, you would be much better uploading a dummy workbook to go with your explanation so people can see your tructure, formatting & formulae.

You select the worksheet, which is unnecessary, but it is only selected if the first condition is true. It might be faster to use AutoFilter in the code, but an example workbook would help.

Please Login or Register  to view this content.

Here is the workbook. I think it selected the worksheet because that part used to be in a module and I thought it would work better under the worksheet_change part.

So when you select a value in column E, it must evaluate to see if it should calculate a value for K, AND determine which cells to cleacontents (in the second part) Also, if a value is typed in column J OR deleted, it must re-evaluate what goes into K by determining if "Annuity" is in column E AND what NEW value is in J (I couldn't get the value in K to disappear when i deleted J, which it should have). And one should NEVER be able to delete or manually change a value in K, K should only be determined by values in E and J. The other problem was that the code used to protect K was an undo function (the module I posted), so when I typed a new positive value into J (which it should re-evaluate for K, it wouldn't let me change the value at all). The reason for all this code is because I need to be able to filter and sort this selection, and when I protected the formula in column K by locking the cells, that was no longer possible. Thanks, hopefully this helps! I will try your code royUK if I get a chance this morning. Thanks!

jman, there is no workbook attached, you attach a workbook by scrolling down from your post window to Manage attachments, browse your files then click upload then post your message.

royUK, your code works a lot better than mine! It is about 3 times faster, and does what it needs to. So I just need 3 more things built upon this.

1. For some reason, when I type a value in J, it unprotects the sheet and doesn't re-protect after the calculation, and it needs to re-protect.

2. Column K value needs to go to 0 when you erase the value in column J or when the macro clears the contents of J. For some reason it's not triggering the first macro, because if it did and column J isblank, then it would make column K "". So we need to get that to trigger somehow.

3. Column K needs to be protected at ALL times. So if you try to delete a cell in column K or change the value in ANY way, it thwarts you and re-establishes the value that was in the cell unharmed. BUT, it must not thwart changes that come from typing a new value in J or deleting a value in J. So basically protect column K just like worksheet cell protection would, but with VB.

Thanks for all your help, it's very close and this is the workbook I've been working on for 6 months now! I think this will finalize it!