If a cell contains a certain "word", then return todays date

[DarkoDeign2]

Hi,

I have been searching the web for a workable formula, but so far I haven't been able to find anything to make use of.

Cell AE2 can be blank, or contain one of three possible words: "Apple", "Car" or "Book".
In cell AG2 I would like to have a formula that returns "todays date" if cell AE2 contains the word "Apple". Furthermore I would like to have the date static, so it doesn't change over time.

Would appreciate if somebody could help me figuring this out.

 

[Joe4]

Sorry for a newbie question but how do I that?
Should I paste it in before the previous code or after previous code?

It makes no difference where you put this code. It is its own separate procedure.
You can put it anywhere. The important thing is to run it.

A little background...
If you note, the code that I created for you has a line like this at the beginning of the code:

Application.EnableEvents = False

What this does is shut off events (like "Worksheet_Change") while the code runs. Why do we do this? To avoid getting caught in possible endless loops.
A "Worksheet_Change" event procedure is VBA code that runs automatically when a cell is manually updated. But if the code itself is making updates to cells (like ours is), those changes will cause the "Worksheet_Change" event procedure to fire again (the code is triggering another iteration of itself to run!). Depending on what the code does and how it is written, you could get caught in an endless loop, and your Excel will lock up! We obviously don't want this to happen!

So, the typical way to handle this is to temporarily turn events off with the line above BEFORE your code updates any cells, and then turn it back on AFTER the cell updates with a line like:

Application.EnableEvents = True

However, if you encounter any errors or breaks in your code where the code shuts off events, but never gets to the line that turns them back on, they will be shut off and your "Worksheet_Change" event procedure will not work automatically anymore until you turn it back on. That is what that little procedure I can you in my last post does.

Alternatively, you could close out of Excel altogether, and then re-open it to reset everything.

 

[DarkoDeign2]

Thanks for the detailed explanation, that helped a lot..

 

[Joe4]

Your welcome.

So, did that fix the issue, or is it still not working?

 

[DarkoDeign2]

Yes, that fixed the issue.

 

[DarkoDeign2]

Everything has worked fined until now. But a couple of days ago and today.
I got an error message saying something like the script has stopped due unexpected error, do you want to continue?
When I clicked yes, all my dates changed to todays date....
The workbook is becoming quite heavy with formulas and pivot tables. Could running out of memory be a potential reason for the script running error?
What would be the best way to try to problem solve this?

 

[Joe4]

Exactly how many sheets, rows, and columns of formulas do you have?

 

[DarkoDeign2]

On the same sheet as the script I have 1500 rows in columns A to AU.
But I don't have formulas in all cells, but I do have conditional formatting on all cells.
Then I have 4 other worksheets in this workbook, but not so many formulas on the other worksheets.

 

[Joe4]

I am not sure what is going on. It is hard to say from my end.
It is possible you may have some corruption going on in your workbook.
Or perhaps you have some bad/unexpected data that is wreaking havoc with some calculations.