×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Hi Looking for some code that tim

Hi Looking for some code that tim

Hi Looking for some code that tim

(OP)
Hi
Looking for some code that timestamps for me.
When Percentage in column H = 100 percent
then Column G corresponding rows will show timestamps when 100% was achieved.

I want this to be static and not recalculate when the workbook is opened a day or two later

Cheers

RE: Hi Looking for some code that tim

Hi,

How does the percent complete get entered in column H?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Thanks for Responding Skip,
Column H is looking at another sheet and adding up progress from various cells, eventually all tasks will be complete and 100% will be achieved.

RE: Hi Looking for some code that tim

“Looking”

A formula?

This is going to be an event driven solution. And it seems that the events are located on some other sheet named ???????

You’ve got to give me some very specific info. The formula would help. The workbook would help.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

Here are the things I did to your workbook:

1) Named the ranges in Sheet1 & Sheet2 based on column headings

2) Changed your formula for readability and maintainability...

G5: =COUNTIFS(Completed,"Y",Category,C5)/COUNTIF(Category,C5)


3) Event code in Sheet2 Code sheet (Right-Click Sheet2 tab/select View Code)...

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'SkipVought 29-01-2018
'change in Completed, calculates the range offset in table in Sheet1
' and puts Date in Time_Stamp if none exists

    Dim sCat As String      'Category value of Completed
    Dim iOff As Integer     'row offset in table on Sheet1
    
    
    If Not Intersect(Target, [Completed]) Is Nothing Then       'value changed in Completed
        sCat = Intersect(Target.EntireRow, [Category]).Value    'corresponding Category value
        
        iOff = Application.Match(sCat, [Cat_Value], 0)          'row offset Sheet1 table
        
        With Sheet1
            If .Range("Actual_Percentage")(iOff).Value = 1 Then   'if 100%
                If .Range("Time_Stamp")(iOff).Value = 0 Then      'if no Time_Stamp
                     .Range("Time_Stamp")(iOff).Value = Date      'then put Date in Time_stamp
                End If
            End If
        End With
    End If
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Thanks Skip
I will go through this and see what I can learn... I will update this thread after I have a look.
Appreciate your inputs

RE: Hi Looking for some code that tim

(OP)
When I update the category column with a "y" I can see the percentage hit 100
But no timestamp is triggered.I have also refreshed data but nothing so far.

RE: Hi Looking for some code that tim

Did you enable macros when you opened your workbook?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Yes Macros are enabled.
I restated my laptop and when I add or remove "y"s from the category I get a type mismatch error on this line.
sCat = Intersect(Target.EntireRow, [Category]).Value 'corresponding Category value


EDIT,
Single entry works , if I drag down multiple "y"s I get the error message or delete multiple "y"s

RE: Hi Looking for some code that tim

Are you using my workbook?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Yes

RE: Hi Looking for some code that tim

When I enter a “y” in Completed, the percent changes on Sheet1 and when the percentage reaches 100%, the Date is placed in the time stamp column.

So I cannot understand what’s happening down under.

Can you right-click the Sheet2 tab and select View Code. Then put a BREAK on the If Not Intersect statement.

Then put a “y” in Completed, and report what happens.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Hi
The line of code turns yellow when I put a break on it and enter a y
To be fair the process works well on one entry at a time. it only has an error if I delete multiple "y"s or drag a y down the list.

RE: Hi Looking for some code that tim

Thanks for the feedback. I modified the code to accommodate multiple cell changes.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'SkipVought 29-01-2018
'change in Completed, calculates the range offset in table in Sheet1
' and puts Date in Time_Stamp if none exists
'30-01-2018 accomodated Target of multiple cells

    Dim sCat As String      'Category value of Completed
    Dim iOff As Integer     'row offset in table on Sheet1
    Dim t As Range
    
    For Each t In Target
        If Not Intersect(t, [Completed]) Is Nothing Then       'value changed in Completed
            sCat = Intersect(t.EntireRow, [Category]).Value    'corresponding Category value
            
            iOff = Application.Match(sCat, [Cat_Value], 0)          'row offset Sheet1 table
            
            With Sheet1
                If .Range("Actual_Percentage")(iOff).Value = 1 Then   'if 100%
                    If .Range("Time_Stamp")(iOff).Value = 0 Then      'if no Time_Stamp
                         .Range("Time_Stamp")(iOff).Value = Date      'then put Date in Time_stamp
                    End If
                End If
            End With
        End If
    Next
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Thanks Skip
Much appreciated, this helps me understand a few things that were a bit blurry to me.

RE: Hi Looking for some code that tim

Great!

Just occurred to me since you’re deleting data in Completed, that you might want the Time_Stamp to be cleared if all the “y” values are deleted for any Category value. Yes?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Yes I did have the same thought. If old stamps are left in there, a new update will not overwrite.
So it would be best on >100% it clearts

RE: Hi Looking for some code that tim

On the Change Event:
   If 0% Then 
      Clear Time_Stamp
   If 100% Then 
      Enter Time_Stamp
 
Yes?

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'SkipVought 29-01-2018
'change in Completed, calculates the range offset in table in Sheet1
' and puts Date in Time_Stamp if none exists
'30-01-2018 accomodated Target of multiple cells
'31-01-2018 clears Time_Stamp if percentage is 0%

    Dim sCat As String      'Category value of Completed
    Dim iOff As Integer     'row offset in table on Sheet1
    Dim t As Range
    
    For Each t In Target
        If Not Intersect(t, [Completed]) Is Nothing Then       'value changed in Completed
            sCat = Intersect(t.EntireRow, [Category]).Value    'corresponding Category value
            
            iOff = Application.Match(sCat, [Cat_Value], 0)          'row offset Sheet1 table
            
            With Sheet1
                Select Case .Range("Actual_Percentage")(iOff).Value
                    Case 0      'reset Time_Stamp
                        .Range("Time_Stamp")(iOff).ClearContents          'clear Time_stamp
                    Case 1      'Assign Time_Stame
                        If .Range("Time_Stamp")(iOff).Value = 0 Then      'if no Time_Stamp
                             .Range("Time_Stamp")(iOff).Value = Date      'then put Date in Time_stamp
                        End If
                End Select
            End With
        End If
    Next
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Cheers Vic
I will run it this morning and have a go.
Thanks for your help.

RE: Hi Looking for some code that tim

I’m Skip, not Vic, but I’m glad you have a satisfactory solution. Thanks for the littlw purple star. I enjoyed crafting this code.

Usually I try to lead the user to write their own code. But since this was event based and a bit more complex in nature than most problems posted here, I elected to generate a turnkey solution. Hope you don’t fell short-changed.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hi Looking for some code that tim

(OP)
Lol , I was channelling the capitol V after Skip in your name, apologies :)
And yes thank you for your efforts, I am self taught on VBA so I really appreciate looking at and understanding this code.
Im constantly amazed at what can be done and how people construct code to get it done.

RE: Hi Looking for some code that tim

I like that you've been self taught. Keep at it. You'll get lots of good help here at Tek-Tips. Come back any time. Yes, Excel is an amazingly versatile tool. I find it exciting and recreational to use and create solutions.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close