Inserting Formula Based On Another Cell Empty Or Not

I have a workbook that I am working on that I wish to insert a time stamp into a cell (on the same row) when there is a value present in column A (it will be a text value). I'm currently using the below code to do that. In the testing phase it worked fine as I entered in values in Column A one cell at a time.

However, when I moved to pasting in several cells (several hundred) at one time, the time stamp was not added. I want it to be automatic and clear the cell when nothing is present in Column A but it has to work whether I enter one cell at a time or whether I paste in several cells at once. Im not sure if a loop needs to be use or what, but would appreciate any assistance.

Please Login or Register  to view this content.

Just thought I would check back and see if anyone had any input. Thanks.

Hi,

Why don't you try to use a function (instead of a procedure) which returns a date and as a parameter a cell is given. Take a look:

Please Login or Register  to view this content.

You call a function from your worksheet the same as a regular built-in function:

Please Login or Register  to view this content.

If Target is a single cell, then Target.Value will return the value in that cell. When Target is a Range of cells, Target.Value doesn't really mean anything anymore, and will normally give you a Run-time error '13' Type Mismatch. The only reason it doesn't here is because of your On Error statement, which simply bypasses the offending line without telling you anything about it. The code in the error handler should only run if there is an error, so there should always be an Exit Sub above it.

To do what you want, you need to do a For/Next loop to go through all the cells in the range, as shown below (note: I took out the UnProtect and Protect, as if I Protected my Worksheet at the end, I couldn't change it anymore to test - I'll assume your worksheet is set up to make that not a problem for you.)

Please Login or Register  to view this content.

Originally Posted by rabbita

Hi,

Why don't you try to use a function (instead of a procedure) which returns a date and as a parameter a cell is given. Take a look:

Please Login or Register  to view this content.

You call a function from your worksheet the same as a regular built-in function:

Please Login or Register  to view this content.

rabbita, thank you for your reply. I tried your suggestion but was not able to get it to work properly. I remembered, after trying your code, that I had tried something similar (using a function) and was not able to get it to work exactly as I had hoped. Its not an error in your code, its just the overall operation doesn't work as I would like. However, thank you very much for taking the time to post your idea.

mgs73, thank you for your reply as well. I tried yours as well and it worked, particularly with pasting in data from another workbook. Thank you also for pointing out my lack of the Exit Sub statement. I am still very new at this and forgot about that. The funny thing about your comment about having to use a loop, I had made a comment to myself in the code when I first observed the procedure not working when I pasted data in, that I would likely have to use a loop to fix that.

Thank you so very much for your help and I apologize it has taken me so long to get back here to reply. I had a few days off from work and, well, this project really did not cross my mind much, LOL.