Issues with timestamp now()

[RJGibson]

Hi All,

Any help attained would be great, I am not the best using VBA so hence the query.

I have a sheet where users submit their completed work and a cell calculates the % of work done. I have 4 other cells that have % thresholds from 25%/50%/75%.

Is there a way for me to have a timestamp generated once the threshold ~% of work complete has been added, apologies on the crude example below cant upload images in work
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]% Complete[/TD]
[TD]25%[/TD]
[TD]50%[/TD]
[TD]75%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]RJ[/TD]
[TD]25[/TD]
[TD]Timestamp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The current code we were using is as follows, is there a way to generate the timestamp one time only in each cell?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range: Set A = Range("A3:A6")
Dim v As String
If Intersect(Target, A) Is Nothing Then Exit Sub

Application.EnableEvents = False
v = Target.Value
If v = "25%" Then Target.Offset(0, 1) = Now()
If v = "50%" Then Target.Offset(0, 2) = Now()
If v = "75%" Then Target.Offset(0, 3) = Now()
If v = "100" Then Target.Offset(0, 4) = Now()
Application.EnableEvents = True
End Sub

Regards,
Ray

 

[RJGibson]

Let's keep walking this back (I am trying to find the "source" of the change).
What is in cell L33? Is this being manually updated, or is it another formula? L33 has another formula
Really, what we are after is the "source" of all these changes. What is the "base" thing that changes that causes all these formulas to update?


And lastly, can you confirm:
- what column has your 25%, 50%, 75%, 100% percentage amounts?
- what column you want the time stamp in?

I appreciate your patience.


L33 is a sum of a range on another worksheet. L33 is updated by formula when work is marked as completed on another sheet manually by a worker when they complete that work. L4 is a set value


The base thing is a worker marks a row complete. that then changes the value of L33. This is turn changes the % number in a cell in Column M


Column O would have a timestamp when a cell in Column M meets the criteria of 25%
Column P would have a timestamp when a cell in Column M meets the criteria of 50%
Column Q would have a timestamp when a cell in Column M meets the criteria of 75%
Column R would have a timestamp when a cell in Column M meets the criteria of 100%


I hope ive explained this better.

 

[Joe4]

OK, this could get very tricky.

As we have seen, the Worksheet_Change event procedure only runs when a cell is manually updated. To trigger code to run off of changes due to formulas, that would be the Worksheet_Calculate.
However, there is one major problem with that. Unlike Worksheet_Change, the Worksheet_Calculate event CANNOT identify which cell on the sheet changed. All it knows is that some calculation happened somewhere on the sheet. So, if we use that, we cannot identify which cell's value changed by formula.

So, as I see it, we have two options on how to approach this:
- Create a Worksheet_Change event procedure that runs on the sheet where you are doing the manual data updates (the sheet that the first formulas runs off of). Depending on your data structure, this could get a little tricky in identifying which cell/rows on the other sheet (where the formulas reside) to run against.
- Use a Worksheet_Calculate event procedure that runs on your formula sheets, but every time it runs, it will need to cycle throw ALL the rows it may apply to, and check each one individually to determine which ones need updating. This might work if once your time stamp fields are updated, they should never change (meaning, you would never have a situation where you would want to change a time stamp that is already populated).

So, based on this information, what would you like to do?

 

[RJGibson]

I think the second option might work as once the timestamp for a % column a new timestamp for that column should not be generated.

 

[Joe4]

OK, adapting the code that Scott originally wrote for you, try this:

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False

    Dim v As Range
    For Each v In Range("M33:M55")
        If v = 0.25 And v.Offset(0, 1) = "" Then v.Offset(0, 1) = Now()
        If v = 0.5 And v.Offset(0, 2) = "" Then v.Offset(0, 2) = Now()
        If v = 0.75 And v.Offset(0, 3) = "" Then v.Offset(0, 3) = Now()
        If v = 1 And v.Offset(0, 4) = "" Then v.Offset(0, 4) = Now()
    Next v
    
    Application.EnableEvents = True
    
End Sub

 

[RJGibson]

I get a compile error when the formula is updated.

Compile Error


invalid Next control variable reference.

 

[Joe4]

Sorry, typo on my part.

It should be

Next v

not

Next cell

 

[RJGibson]

You are a gentleman and a wizard sir! Thank you very much

 

[Joe4]

You are welcome.
Glad we were able to help you.