添加向下钻取并添加时间戳(Add a drill down and add a time stamp)

添加向下钻取并添加时间戳(Add a drill down and add a time stamp)

我有这个电子表格来协调数据。 因此，一旦我收到最终数据，我就会将其与内部系统和外部系统进行协调。

我想添加到电子表格的是两列，一旦数据为“最终”或“已审核”，即可记录：

AC列：我有一个向下钻取“最终”或“已审核”列AD：我想要添加时间戳

我有以下Excel VBA代码，但是一旦选择了Final，它会在与向下钻取相同的单元格上标记时间戳。

Private Sub Worksheet_Change(ByVal Target As Range) Dim fn As Integer Dim ts As Integer ActiveSheet.Unprotect If Not Intersect(Target, Range("AC7:AC42")) Is Nothing Then fn = [AC7:AC42].Find(Target.Value).Column If Cells(Target.Row, fn) = "Final" Then Cells(Target.Row, fn).Value = Now Cells(Target.Row, fn).NumberFormat = "mm/dd/yy hh:mm AM/PM" Cells(Target.Row, fn).Locked = False End If End If ActiveSheet.Unprotect End Sub

因此，如果我从同一列单元格中的“向下钻取”列表中选择“最终”，则此代码现在添加TIMESTAMP。

我想在列AC中选择向下钻取，在列AD上选择时间戳。

我知道我错过了一个步骤或一段代码，但却无法理解。

I have this spreadsheet to reconcile data. So once I received final data, I reconciled it to an internal system vs external system.

What I am trying to add to the spreadsheet is two columns to record once data is "Final" or "Under Reviewed":

Column AC: I have a Drill Down with "Final" or "Under Reviewed" Column AD: is where I want to add the Time Stamp

I have the following Excel VBA code, but it stamps the time stamp on same cell as the drill down once Final is selected.

Private Sub Worksheet_Change(ByVal Target As Range) Dim fn As Integer Dim ts As Integer ActiveSheet.Unprotect If Not Intersect(Target, Range("AC7:AC42")) Is Nothing Then fn = [AC7:AC42].Find(Target.Value).Column If Cells(Target.Row, fn) = "Final" Then Cells(Target.Row, fn).Value = Now Cells(Target.Row, fn).NumberFormat = "mm/dd/yy hh:mm AM/PM" Cells(Target.Row, fn).Locked = False End If End If ActiveSheet.Unprotect End Sub

So this code right now adds the TIMESTAMP if I select "Final" from the Drill Down list in the same column cell.

I want the drill down selection in Column AC and the Time Stamp on Column AD.

I know I am missing a step or a block of code but just can't figure it out.

最满意答案

Private Sub Worksheet_Change(ByVal Target As Range) Dim fn As Integer Dim ts As Integer If Target.Cells.CountLarge > 1 Then Exit sub If Not Intersect(Target, Me.Range("AC7:AC42")) Is Nothing Then If Target.Value = "Final" Then ActiveSheet.Unprotect With Target.EntireRow.Cells(1, "AD") .NumberFormat = "mm/dd/yy hh:mm AM/PM" .Value = Now .Locked = False End with ActiveSheet.Unprotect '<<<Protect? End If 'is Final End If 'is in AC End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim fn As Integer Dim ts As Integer If Target.Cells.CountLarge > 1 Then Exit sub If Not Intersect(Target, Me.Range("AC7:AC42")) Is Nothing Then If Target.Value = "Final" Then ActiveSheet.Unprotect With Target.EntireRow.Cells(1, "AD") .NumberFormat = "mm/dd/yy hh:mm AM/PM" .Value = Now .Locked = False End with ActiveSheet.Unprotect '<<<Protect? End If 'is Final End If 'is in AC End Sub