Consulting

Results 1 to 18 of 18

Thread: Solved: Date stamp in column L if forumlua in column W = 1

  1. #1
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location

    Solved: Date stamp in column L if forumlua in column W = 1

    Hello,

    Could someone help me with a worksheet change event, such that when a cell in column W (where W2=F2/G2, etc.) changes to 1, a today's date is posted in column L?

    The column with the cell I will acutally change is F. So, when I change F2 to "4" (and G2 is "4"), W automatically becomes "1", and when W is "1", L should change to today's date (indicating the date when all 4 reviews on a paper came in).

    Thank you,
    Natasha

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    [VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x As Date
    If Range("w4").Value = 1 Then
    Range("L4") = Date
    End If
    End Sub[/VBA]
    Peace of mind is found in some of the strangest places.

  3. #3
    VBAX Tutor lynnnow's Avatar
    Joined
    Jan 2005
    Location
    Mumbai, Maharashtra, India
    Posts
    299
    Location
    vlina and Austenr,

    IMO I prefer the Now() command better, since it shows the date and time the change was made.

    HTH

  4. #4
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location

    Entire column

    Thank you, both, for your help. This works for row 4. How could I extend it to the entire column?

    Thanks again,
    natasha

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    [VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x As Date
    If Range("W:W").Value = 1 Then
    Range("L:L") = Now()
    End If
    End Sub[/VBA]
    Peace of mind is found in some of the strangest places.

  6. #6
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location
    Thanks - I had tried that, but excel doesn't seem to like it. It highlighted the "If Range ("W:W")... " line for debugging.

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Change the "If Range ("W:W")..." to IF Range("W":"W").... and see what happens
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x As Date
    If Not Intersect(Target, Me.Range("W:W")) Is Nothing Then

    If Target.Value2 = 1 Then

    Me.Cells(Target.Row, "L").Value = Now()
    End If
    End If
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location
    Changing it to "W":"W" gave me the following error:

    Compile error:
    Expected: list separator or )
    xld, it worked if I manually change the W column to be "1". However, the W column is acutally a formula (something like =F5/G5, for example). The only number I change is in the F column, and W will equal (or show) "1" if F=G.

    Thanks again for your help,

    Natasha

  10. #10
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    Hai try this

    For i = 1 To 65000
    
    a = Cells(i, 23).Value
    
        If a = 1 Then
        
            Cells(i, 12).Value = Date
        
        End If
    
    Next i

  11. #11
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Quote Originally Posted by mohanvijay
    Hai try this

    For i = 1 To 65000
    
    a = Cells(i, 23).Value
    
        If a = 1 Then
        
            Cells(i, 12).Value = Date
        
        End If
    
    Next i
    or this:

    [vba]LastRowColA = Range("A65536").End(xlUp).Row

    For i = 1 To LastRowColA

    a = Cells(i, 23).Value

    If a = 1 Then

    Cells(i, 12).Value = Date

    End If

    Next i[/vba]

    This will not process all 65000 + rows
    Peace of mind is found in some of the strangest places.

  12. #12
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    A shot:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
        
        If Not Application.Intersect(Target, Range("F:F")) Is Nothing Then
            
            For Each rCell In Target
                If rCell.Column = 6 _
                And rCell.Value = rCell.Offset(, 1).Value _
                And Not rCell.Value = vbNullString Then
                    rCell.Offset(, 6).Value = Format(Date, "mmm d yyyy")
                ElseIf rCell.Column = 6 Then
                    rCell.Offset(, 6).ClearContents
                End If
            Next
        End If
    End Sub
    Hope that helps,

    Mark

  13. #13
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location
    Thank you for your help! Unfortunately, these don't seem to work - nothing happens on the worksheet.

  14. #14
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Bobs code or Marks should work. Where are you putting the code. It has to go in the worksheet that you have your data in in the OP.

    In the VBA editor (Alt + F11) double click the sheet your data is in from the VBA Project pane.

    Next in the bigger window on the right where you see (General) click the drop down and change it to Worksheet. This will automatically bring up the Worksheet_Selection Change event. That is where the code goes. Have you tried that?
    Peace of mind is found in some of the strangest places.

  15. #15
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location
    Yup, that's where I put it.

    It doesn't seem to like the "If a = ..." part. Do you know of any reason why that might be?

  16. #16
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    try this instead:

    [vba] Dim x As Date
    If
    Not Intersect(Target, Me.Range("W:W")) Is Nothing Then
    If Target.Value2 = 1 Then
    Me.Cells(Target.Row, "L").Value = Now()
    End
    If
    End
    If [/vba]
    Peace of mind is found in some of the strangest places.

  17. #17
    VBAX Regular
    Joined
    Aug 2010
    Posts
    13
    Location
    Thank you, everyone and austenr for your help! It's much appreciated and helpful for learning.

    It looks like the "a =" didn't work because of the data type.

    GTO's code did work in the end (I must have put it in SelectionChange rather than regular Worksheet Change when I first tried).

    Thank you again and hope to be able to repay the favor one day!

  18. #18
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Glad you got your problem solved. Please mark your thread solved. Thanks.
    Peace of mind is found in some of the strangest places.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •