Update datestamp based on formula in adjacent cell

[nuttyengineer]

Hello everyone,

Just seeking some VBA help in solving this problem.

I am currently building a database which has a formula which evaluates in e.g. range D1:D4 if the item is still "Open" or if it is "Closed" based on certain criteria in the corresponding row.
e.g. in each cell in range in column D I have the following formula =IF(AND($A1="Closed",$B1="Closed"),"Closed","Open")

How can I update the adjacent cell in column E with the datestamp when the above formula in the cell in column D evaluates "Closed". Any ideas/ help would be appreciated.

Requirements:
1. When formula in column D evaluates "Closed" adjacent cell in column E is updated with current datestamp.
2. When formula in column D evaluates "Open" adjacent cell in column E is cleared if not already blank
3. When formula in column D cannot evaluate "Closed" or "Open" for whatever reason e.g. formula is deleted) adjacent cell in column E is cleared if not already blank

I have previously used a VBA Worksheet_Change event for other similar problems and this works fine if the data in column D is directly entered/pasted in cell but this won't work if the data in column D is based on formula calculation or if the datestamp should be updated only for specific text value. See code below:

Please Login or Register  to view this content.

[JBeaucaire]

This is how I would write that:

Please Login or Register  to view this content.

[nuttyengineer]

Hi JBeaucaire,

Thank you for your reply and for your suggestion for improving the current code.

I have tried this and it works, as previous, if the data is entered directly in range D then the date is updated.

However if I put a formula in range D to evaluate "Closed" it still does not work.

Any ideas to address original problem?

[JBeaucaire]

No, ws_change events are designed to watch the cells you change. A formula doesn't qualify.

More dripping plumbing, but I suppose we could make it so anytime any cell anywhere was changed at all, then all of the cells D1:D4 are examined for their current values, so this macro would be running all the time...

Please Login or Register  to view this content.

[DonkeyOte]

@JB, I suspect from a logic perspective that won't work given the stamp will update whenever a cell is changed and the value is Closed irrespective of whether or not the value was Closed prior to the change.
Though I concede whether or not this is desired is unclear given the use of "Evaluate to" ... I suspect it should only update when it first becomes Closed.

Assuming the above is correct...

Upon calculation of sheet you can compare the current values to the cached values * where they differ and the current value is Closed update the Stamp.

* You can cache the values in D1:D4 after the above check by either storing in static variables or (better) storing in another range as values only.

The alternative is to work back and establish the prime mover (manual alteration)

We know the formula in D is:

=IF(AND($A1="Closed",$B1="Closed"),"Closed","Open")

if A1:B4 are altered manually the Change event can be adjusted to work off A1:B4 ... if they themselves are results of formulae then you need to work backwards to establish "prime mover(s)"

[JBeaucaire]

Thanks DonkeyOte, that was an unplanned omission on my part from the first macro to the last.

I've edited the code in post #4 to make sure the date cell is empty before updating it.

[nuttyengineer]

So if I understand correctly there is no 'straight-forward' approach to use a similar event as the Worksheet_Change to pick-up a new value in a calculated cell.

@DonkeyOte: thank you for your suggestions.

Upon calculation of sheet you can compare the current values to the cached values * where they differ and the current value is Closed update the Stamp.

* You can cache the values in D1:D4 after the above check by either storing in static variables or (better) storing in another range as values only.

I have tried this approach and I think I have something going now. I was trying as much as possible to not have a 'cache' column in the worksheet as this sheet will be used as a database and will grow to a significantly large worksheet.

The alternative is to work back and establish the prime mover (manual alteration)

Now this alternative is yet to be explored... We know the formula in D is:

=IF(AND($A1="Closed",$B1="Closed"),"Closed","Open") however, what I haven't said as yet is that A1 and B1 are manually selected from drop-down validation lists,,,,,,

Is it possible to therefore update the date in E1 when A1 = 'Closed' AND B1 = 'Closed'?
Any examples of code for this scenario?

@JBeaucaire , thank you for your updated code. I will have to test this approach too. Many thanks.

[JBeaucaire]

Now this alternative is yet to be explored... We know the formula in D is:

=IF(AND($A1="Closed",$B1="Closed"),"Closed","Open")

...however, what I haven't said as yet is that A1 and B1 are manually selected from drop-down validation lists.

Is it possible to therefore update the date in E1 when A1 = 'Closed' AND B1 = 'Closed'?
Any examples of code for this scenario?

Assuming the columns A and B need to be watched as far down as you wish to go, this is how I would write that:

Please Login or Register  to view this content.

Note, there's no need for a formula in column D with this.

[royUK]

Why not the Calculate event?

Please Login or Register  to view this content.

[DonkeyOte]

Roy, this goes back to the earlier point - you would need to cache pre calculation values.
If you don't you have no means of determining as to whether or not the current calculation has generated a new "Closed" status (ie stamp to update) or whether it was "Closed" already (no stamp update)

For that reason, IMO, where you can - it's nearly always preferable to use the Change event given Target availability.

[royUK]

I missed that point DO

[nuttyengineer]

Well I just put together a file to show examples of different ways of solving this problem (see tabs Example 1 - 3 in attached file). However I am still not clear as to how to solve using the 'prime movers' and VBA only as suggested previously by DO.
Ideally I would like to use VBA to update both the 'Overall Status and 'Date' in one go.
I'm afraid I couldn't get JBeaucaire's code [posted 10-04-2010 08:30 PM] to work.

Please see tab Example 4 in attached file for template for what I would like to achieve.
I am still very new to VBA so this is pushing my limits. Can anyone help?

[JBeaucaire]

Using columns A:C and checking to make sure all 3 are set to "closed" before stamping the row closed, this works in the sheet module:

Please Login or Register  to view this content.

[nuttyengineer]

JBeaucaire
Many many thanks! With a few tweaks I was able to use your example code to do what I want it to.

Thank you all for your contribution to resolving this problem / discussion.