Permanent Date Stamp Macro

It looks like it got caught on Sub DateStamp()

what does this mean?

What was the error message your received?

I tested DBY's code and it worked for me. Perhaps you need to post your spreadsheet to determine why it is not working for you.

Look at the attached picture, then click the reply button and you will get another button that says go advanced.

Click the go advanced button.

Do what it shows in the picture I attached.

ViewPic

Any Excel formula you would use would change each time the date changes.

The code I will give you puts the date into the cell at the point and time you change the cell to a 3.

If you change the cell from 3 to say 7 on a different date, the date will remain as was, but will change again if the cell is changed back to a 3 again. I could also have it clear a cell if you change from a 3 to a different number.

If this is not the behavior you want then you need to specify what you want to happen when.

Another option is a button that runs a macro that checks all the cells when the macro is changed and puts the date in then, but maybe this isn't the behavior you want.

Right click on the "Contract Data" tab and choose view code. Paste the code below into the white window.
Save the file as a macro enabled type .xlsm, the macro will run whenever you change a value in the B column to 3.

Please Login or Register  to view this content.

Well as far as row 78 I don't know without seeing it. As far as the other stuff I clearly pointed out how this works in my post #13, did you read it?

Maybe we're on a different page, so let me explain the code and make sure we're on the same page.

Firstly the code is triggered by the worksheet change event for the "Contract Data" sheet. What this means is if you change any cell on that sheet the code is triggered. It will not be triggered if that change is the result of a formula. So if you are physically typing in a number in a cell then the code will be triggered.

When the code is triggered the cell that was changed is what is called the target. You will see that in the code. I check that the target wasn't in row1 since you have headers, if it is you changed a header and we don't want to run the code.

I check if the target was in column 2, which is what we want, it it's not in column 2, then again we don't want to run the code.

Finally I check if you changed more than 1 cell. You can select a bunch of cells and type a number and hit control + enter and they will all change to that number. You don't want to do that with this sheet or the code won't run right because I wrote code that if you do this the code won't run. That is the Target.Count part you see in the code.


So if all that checks out then the code checks if the target cell value was changed to 3. So the code checks what the cell was changed to.
If this is the case then it goes to the same cell on the other sheet and puts a time stamp in that cell.

So here is a scenario. You have a cell on contract data with a 3 already in it and a cell on the other sheet with a time stamp. You change the 3 to something else and the old time stamp stays. I can remove this if you want by simply changing the code to say if the number that changed on the contract data sheet is not a 3 then clear the same cell on the other sheet.

The code is not made to run through your sheet and look for 3's and add a time stamp because that doesn't make sense to me. A time stamp is supposed to be when an event took place.

If you want code that goes through every cell in the contract data at the push of a button then the question is how do we handle any cells that already had a time stamp on the first sheet. Replace them all, check if there is a time stamp in the cell already. I need to know what behavior you want to see and why.

Knowing now how this works, I think maybe what you saw was perhaps because you didn't understand when the code was supposed to run and what is was supposed to do?

If not, explain to me what you did and what happened so that I can recreate it.

So for example, Row 35 on Product data gets date stamped and then 2 weeks later that record is deleted by our vendor...will the code still be able to run?

Remember the code runs based on you changing a value in "Contract Data" so whatever is deleted is deleted and whatever is not deleted is still there and untouched.

So my question is are the client numbers on "Product Data" always contiguous like that?

The reason I ask is it's much faster for me to write code that looks for the first 7 and then the last 7 and then it knows to time stamps those 2 cells and everything in between.

If not, I have to loop every cell in that column and check its value.

Both versions.

Looping:

Please Login or Register  to view this content.

Range.Find:

Please Login or Register  to view this content.