PDA

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

GTO
08-25-2010, 10:17 AM
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.