我嘗試創建一個歷史資料庫，有點。
我所做的是如果 Sheet1 C1 中的值被更新，單元格 A1 中的先前值將被復制到 Sheet2 C1。如果我再次更新 Sheet1 A1 中的值，該值將再次復制到 Sheet2 C2，在上一個副本的下方，依此類推。同樣，當 Sheet1 C2 中的值被更新時，它將被復制到 Sheet1 C1 值的前一個副本之下。
現在，更新值后，我要創建的是更新的值的標題也被復制到Sheet2 B1，與值復制機制相同。謝謝你。
代碼如下：
Private Sub worksheet_change(ByVal target As Range)
If target.Column = 3 Then 'Determine which column is input cell
Dim col As Long
Dim intlastrow As Long
col = target.Column 'Determine which row/column is output cell
intlastrow = Sheet4.Cells(Rows.Count, col).End(xlUp).Row
If intlastrow = 1 Then 'Populate Row 1 or increment
If Sheet4.Cells(intlastrow, col).Value <> "" Then
intlastrow = intlastrow 1
End If
Else
intlastrow = intlastrow 1
End If
Sheet4.Cells(intlastrow, col) = target.Value
End If
End Sub
uj5u.com熱心網友回復：
更改最少的解決方案：
你已經非常接近你所擁有的，但這是我的解決方案，盡可能少的改變：
Private Sub worksheet_change(ByVal target As Range)
If target.Column = 3 Then 'Determine which column is input cell
Dim col As Long
Dim intlastrow As Long
Dim sh_History As Worksheet
Set sh_History = ThisWorkbook.Sheets("Sheet2") ' You seem to be using Sheet4, but I _
' don't have a Sheet4, so I set an object variable and use whatever name I give the _
' worksheet.
col = target.Column 'Determine which row/column is output cell
intlastrow = sh_History.Cells(Rows.Count, col).End(xlUp).Row
If intlastrow = 1 Then 'Populate Row 1 or increment
If sh_History.Cells(intlastrow, col).Value <> "" Then
intlastrow = intlastrow 1
End If
Else
intlastrow = intlastrow 1
End If
With sh_History.Cells(intlastrow, col) ' With statements are GREAT for shortening code!
.Value = target.Value ' This handles what you already had -- the Date column.
.Offset(0, -1).Value = target.Offset(0, -1).Value ' This handles your Title column.
End With
End If
End Sub
當我測驗它時，它起作用了。
其他建議
評論 1
你有兩行似乎多余的：
If target.Column = 3 Then...
和
col = target.Column
這沒有錯，但對我來說似乎很奇怪，因為您已經用If陳述句完成了測驗，并且已經確定此時target.Column等于 3。那么為什么不直接設定col = 3呢？事實上，由于這段代碼完全獨立運行，我會在第一次宣告變數時設定該變數。
評論 2
我不太了解您對空單元格的處理方式，特別是本節：
If intlastrow = 1 Then 'Populate Row 1 or increment
If sh_History.Cells(intlastrow, col).Value <> "" Then
intlastrow = intlastrow 1
End If
Else
intlastrow = intlastrow 1
End If
您不想在可能放置標題“日期”的第 1 行中放置任何值；并且您不想覆寫以前的條目。我正在想一個你不能洗掉上面那部分的原因，只需更改這一行：
intlastrow = sh_History.Cells(Rows.Count, col).End(xlUp).Row
是這樣的：
intlastrow = sh_History.Cells(Rows.Count, col).End(xlUp).Row 1
上面的行將確保您永遠不會得到第 1 行，也不會用資料覆寫單元格。唯一的問題是，如果您嘗試跟蹤清空單元格的歷史記錄，因為上面的最小更改代碼將在您洗掉日期時生成標題的臨時記錄——但隨后它會被下一次更改覆寫。
這讓我們...
評論 3
猜測您可能想要跟蹤每個更改——甚至洗掉——然后您需要跟蹤寫入歷史記錄的最后一行。您可以通過檢查 Title 和 Date 列的最后一行并選擇最大值來做到這一點。我的首選方法是為每次更改寫一個時間戳，然后只使用該列和行進行遞增。
建議的解決方案：
將上述想法納入重寫的 sub 中，以下是我認為您想要的更簡潔的內容：
Private Sub worksheet_change(ByVal target As Range)
Dim col As Long
Dim intlastrow As Long
Dim sh_History As Worksheet
col = 3 ' Set the var to 3 here and if you need to change it, only one spot needs updating.
Set sh_History = ThisWorkbook.Sheets("Sheet2") ' object variable set
If target.Column = col Then ' Test target against desired column.
intlastrow = sh_History.Cells(Rows.Count, col 1).End(xlUp).Row 1
' Two changes in above line:
' 1). Incrementing the row by 1 right here so I can eliminate the previous IF statements.
' 2). I am adding a Timestamp column so you can track deletions, and to reference the Timestamp _
' I am going to column D, or ' col 1 ' to ensure I find the last row.
With sh_History.Cells(intlastrow, col) ' With statements are GREAT for shortening code!
.Value = target.Value ' This handles what you already had -- the Date column.
.Offset(0, -1).Value = target.Offset(0, -1).Value ' This handles your Title column.
.Offset(0, 1).Value = Now() ' This is the TimeStamp column.
End With
End If
End Sub
uj5u.com熱心網友回復：
最好還確保您的代碼可以處理多單元格更改：
Private Sub worksheet_change(ByVal target As Range)
Dim rng As Range, c As Range
Set rng = Application.Intersect(Me.Columns(3), target)
If rng Is Nothing Then Exit Sub
For Each c In rng.Cells
Sheet4.Cells(Rows.Count, c.Column).End(xlUp).Offset(1, 0).Resize(1, 2).Value = _
c.Offset(0, -1).Resize(1, 2).Value
Next c
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/401791.html
上一篇：將范圍轉換為json陣列