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

 

[Joe4]

So, does the revised code that Scott provided work (once you fix the range reference I mentioned in my last post)?
If not, please describe exactly how it is not working, and what exactly is in column M (values or formulas).

 

[RJGibson]

The code scott provided works provided im manually changing the value of the cell however the completed sheet will be updating that range with a formula and this is where ive got stuck now.

 

[Joe4]

So, there are formulas in column M?
What exactly do those formulas look like (please post one)?

 

[RJGibson]

So, there are formulas in column M?
What exactly do those formulas look like (please post one)?

=IFERROR(M33/100*100,"") This turns the percentage into the number the VBA looks for.

This formula references the cell to the left of it where the % number is calculated by the following =L33/L4 where L4 is the number of total assigned tasks. and L33 being total completed tasks and is displayed as a percentage.

 

[RJGibson]

https://ibb.co/0ZxBxwt

Hope this helps.

 

[Joe4]

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?
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?

 

[Joe4]

https://ibb.co/0ZxBxwt

Hope this helps.

Sorry, workplace security blocks all those sites and file downloads.

There are tools you can use to post screen images, if you elect to do so. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[RJGibson]

Ok. Lets try and work this out. Sorry if I am not being clear. Working off one particular cell which is M33. Worker A is assigned 20 tasks. M:33 contains the formula =L33/L4 Which outputs a percentage number based on the completed work. The cell N:33 contains the formula =IFERROR(M33/100*100,"") to convert the percentage into an integer so VBA can see it using the formula Scott provided. If I manually change M33 the script works. I am trying to figure out if we can get it to work if Worker A changes any values and increases the percentage number in M33 for VBA too see that change and provide a time stamp at each threshold stated.

 

[RJGibson]

 

[Joe4]

I know what you are trying to do, and I am trying to help you. But in order to do so, it is very important that you answer the exact questions that I ask.
Please look back at post 16 I posted above and answer ALL the questions I asked there.