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.

 

[rkb1510]

Go to the sheet that you want to apply this to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   See if any updates to specified range
    Set rng = Intersect(Range("AE2:AE1500"), Target)
   
'   Exit sub if no changes to watched range
    If rng Is Nothing Then Exit Sub
   
'   Loop through changed cells
    Application.EnableEvents = False
    For Each cell In rng
'       See if cell is set to "Apple"
        If cell.Value = "Apple" Then
'           Add date stamp to column AG of same row
            cell.Offset(0, 2).Value = Date
        End If
    Next cell
    Application.EnableEvents = True
   
End Sub

This VBA code will automatically run whenever you make updates to the range AE2:AE1500.

The code should not affect the performance of your workbook, even for 1500 cells.

Hey Joe, Thanks for providing piece of code. This helped me a lot to achieve my task. I am also looking for some modification in this.
If cell value changed from apple to something else I want to keep inserted date as it is and when I change that value again to apple I want to add another date in same cell by keeping previous cell. is that possible to do?

 

[Joe4]

Hey Joe, Thanks for providing piece of code. This helped me a lot to achieve my task. I am also looking for some modification in this.
If cell value changed from apple to something else I want to keep inserted date as it is and when I change that value again to apple I want to add another date in same cell by keeping previous cell. is that possible to do?

That question adds a new level of complexity that requires additional code procedures to track previous values (you cannot do it within the procedure code that I provided).
Since that really is a new question, it would be best to post it as a new question to your own thread instead of posting to someone else's old thread.
That way, it will also appear as a new unanswered question and will show up on the "Unanswered threads" listing that many people use to look for new questions to answer.
So it will get a lot more looks, and has a better chance of being answered, especially if I am away (like I was yesterday) or am not available at the moment.

So I encourage you to create a new thread with your question. You can provide a link back to this one, if you think it is useful (though basic Date Stamping in VBA, like shown in my reply, is a common topic that most experienced VBA programmers are familiar with with).