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

 

[Scott T]

Try

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 = 0.25 And Target.Offset(0, 1) = "" Then Target.Offset(0, 1) = Now()
If v = 0.5 And Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now()
If v = 0.75 And Target.Offset(0, 3) = "" Then Target.Offset(0, 3) = Now()
If v = 1 And Target.Offset(0, 4) = "" Then Target.Offset(0, 4) = Now()
Application.EnableEvents = True
End Sub

Note by putting the values in quotes you turn them into text strings and not numbers "25%" is a text string .25 is a number

 

[RJGibson]

Sorry about the late reply this was perfect thank you!!

 

[Scott T]

You're welcome

 

[RJGibson]

Quick question. I can get this to work if im manually changing a cell in the range. Is there a way to make it work if the cell is updated from another source?

 

[Scott T]

The code above will run if a cell in column A is change. If column A has formulas in it when the formula updates will not run the code. The code would have to be changed to look at the cell that actually updated.

 

[RJGibson]

I see, so if my Range is for instance M33:M55 would I have to change the code to look at M33, M34, M35 and so on?. I honestly have no idea how that is done.

 

[Scott T]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range: Set A = Range("M33:A35")
Dim v As String
If Intersect(Target, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = Target.Value
If v = 0.25 And Target.Offset(0, 1) = "" Then Target.Offset(0, 1) = Now()
If v = 0.5 And Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now()
If v = 0.75 And Target.Offset(0, 3) = "" Then Target.Offset(0, 3) = Now()
If v = 1 And Target.Offset(0, 4) = "" Then Target.Offset(0, 4) = Now()
Application.EnableEvents = True
End Sub

 

[Joe4]

Dim A As Range: Set A = Range("M33:A35")

I think you mean:

Dim A As Range: Set A = Range("M33:[COLOR=#ff0000][B]M[/B][/COLOR]35")

I see, so if my Range is for instance M33:M55 would I have to change the code to look at M33, M34, M35 and so on?. I honestly have no idea how that is done.

Which columns are you checking for values and which columns are you wanting to update now?

 

[RJGibson]

ok so im checking column M:33 > M:55 for change in values then entering the timestamp as the offset like a timestamp progress bar