View Full Version : Solved: Date stamp in column L if forumlua in column W = 1
vlina
08-23-2010, 01:54 PM
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
austenr
08-23-2010, 02:28 PM
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
lynnnow
08-24-2010, 02:01 AM
vlina and Austenr,
IMO I prefer the Now() command better, since it shows the date and time the change was made.
HTH
vlina
08-24-2010, 10:05 AM
Thank you, both, for your help. This works for row 4. How could I extend it to the entire column?
Thanks again,
natasha
austenr
08-24-2010, 01:58 PM
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
vlina
08-24-2010, 02:01 PM
Thanks - I had tried that, but excel doesn't seem to like it. It highlighted the "If Range ("W:W")... " line for debugging.
Aussiebear
08-25-2010, 02:23 AM
Change the "If Range ("W:W")..." to IF Range("W":"W").... and see what happens
Bob Phillips
08-25-2010, 02:42 AM
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
vlina
08-25-2010, 06:53 AM
Changing it to "W":"W" gave me the following error:
Compile error:
Expected: list separator or )
xld (http://www.vbaexpress.com/forum/member.php?u=2139), 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
mohanvijay
08-25-2010, 07:58 AM
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
austenr
08-25-2010, 08:07 AM
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:
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
This will not process all 65000 + rows
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
vlina
09-14-2010, 08:30 AM
Thank you for your help! Unfortunately, these don't seem to work - nothing happens on the worksheet.
austenr
09-14-2010, 08:43 AM
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?
vlina
09-14-2010, 09:02 AM
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?
austenr
09-14-2010, 09:28 AM
try this instead:
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
vlina
09-14-2010, 01:33 PM
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!
austenr
09-14-2010, 01:41 PM
Glad you got your problem solved. Please mark your thread solved. Thanks.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.