我有这个电子表格来协调数据。 因此,一旦我收到最终数据,我就会将其与内部系统和外部系统进行协调。
我想添加到电子表格的是两列,一旦数据为“最终”或“已审核”,即可记录:
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 SubSo 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更多推荐
发布评论