如何在Excel中記住或保存已更改單元格的先前單元格值？

通常，當(dāng)用新內(nèi)容更新單元格時(shí)，會(huì)覆蓋以前的值，除非在 Excel 中撤消操作。 但是，如果您想保留先前的值以與更新的值進(jìn)行比較，將先前的單元格值保存到另一個(gè)單元格或單元格注釋中將是一個(gè)不錯(cuò)的選擇。 本文中的方法將幫助您實(shí)現(xiàn)它。

在Excel中使用VBA代碼保存以前的單元格值

在Excel中使用VBA代碼保存以前的單元格值驚人的！ 在 Excel 中使用高效的選項(xiàng)卡，如 Chrome、Firefox 和 Safari！每天節(jié)省50％的時(shí)間，并減少數(shù)千次鼠標(biāo)單擊！

假設(shè)您有一個(gè)表格，如下所示。 如果C列中的任何單元格發(fā)生更改，則要將其先前的值保存到G列的相應(yīng)單元格中，或自動(dòng)保存在注釋中。 請(qǐng)按照以下步驟進(jìn)行操作。

1.在工作表中包含更新時(shí)將保存的值，右鍵單擊工作表選項(xiàng)卡，然后選擇 查看代碼 從右鍵單擊菜單中。 看截圖：

2.在開幕 Microsoft Visual Basic應(yīng)用程序 窗口，將下面的VBA代碼復(fù)制到“代碼”窗口中。

以下VBA代碼可幫助您將指定列的先前單元格值保存到另一列中。

VBA代碼：將先前的單元格值保存到另一個(gè)列單元格中

Dim xRg As RangeDim xChangeRg As RangeDim xDependRg As RangeDim xDic As New DictionaryPrivate Sub Worksheet_Change(ByVal Target As Range)Dim I As LongDim xCell As RangeDim xDCell As RangeDim xHeader As StringDim xCommText As StringOn Error Resume NextApplication.ScreenUpdating = FalseApplication.EnableEvents = FalsexHeader = "Previous value :"x = xDic.KeysFor I = 0 To UBound(xDic.Keys)Set xCell = Range(xDic.Keys(I))Set xDCell = Cells(xCell.Row, 7)xDCell.Value = ""xDCell.Value = xDic.Items(I)NextApplication.EnableEvents = TrueApplication.ScreenUpdating = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim I, J As LongDim xRgArea As RangeOn Error GoTo Label1If Target.Count > 1 Then Exit SubApplication.EnableEvents = FalseSet xDependRg = Target.DependentsIf xDependRg Is Nothing Then GoTo Label1If Not xDependRg Is Nothing ThenSet xDependRg = Intersect(xDependRg, Range("C:C"))End IfLabel1:Set xRg = Intersect(Target, Range("C:C"))If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) ThenSet xChangeRg = Union(xRg, xDependRg)ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) ThenSet xChangeRg = xDependRgElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) ThenSet xChangeRg = xRgElseApplication.EnableEvents = TrueExit SubEnd IfxDic.RemoveAllFor I = 1 To xChangeRg.Areas.CountSet xRgArea = xChangeRg.Areas(I)For J = 1 To xRgArea.CountxDic.Add xRgArea(J).Address, xRgArea(J).FormulaNextNextSet xChangeRg = NothingSet xRg = NothingSet xDependRg = NothingApplication.EnableEvents = TrueEnd SubCopy

要在注釋中保存以前的單元格值，請(qǐng)應(yīng)用以下VBA代碼

VBA代碼：在注釋中保存以前的單元格值

Dim xRg As RangeDim xChangeRg As RangeDim xDependRg As RangeDim xDic As New DictionaryPrivate Sub Worksheet_Change(ByVal Target As Range)Dim I As LongDim xCell As RangeDim xHeader As StringDim xCommText As StringOn Error Resume NextApplication.ScreenUpdating = FalseApplication.EnableEvents = FalsexHeader = "Previous value :"For I = 0 To UBound(xDic.Keys)Set xCell = Range(xDic.Keys(I))If Not xCell.Comment Is Nothing Then xCell.Comment.DeleteWith xCell.AddComment.Comment.Visible = False.Comment.Text xHeader & vbCrLf & xDic.Items(I)End WithNextApplication.EnableEvents = TrueApplication.ScreenUpdating = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim I, J As LongDim xRgArea As RangeOn Error GoTo Label1If Target.Count > 1 Then Exit SubApplication.EnableEvents = FalseSet xDependRg = Target.DependentsIf xDependRg Is Nothing Then GoTo Label1If Not xDependRg Is Nothing ThenSet xDependRg = Intersect(xDependRg, Range("C:C"))End IfLabel1:Set xRg = Intersect(Target, Range("C:C"))If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) ThenSet xChangeRg = Union(xRg, xDependRg)ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) ThenSet xChangeRg = xDependRgElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) ThenSet xChangeRg = xRgElseApplication.EnableEvents = TrueExit SubEnd IfxDic.RemoveAllFor I = 1 To xChangeRg.Areas.CountSet xRgArea = xChangeRg.Areas(I)For J = 1 To xRgArea.CountxDic.Add xRgArea(J).Address, xRgArea(J).TextNextNextSet xChangeRg = NothingSet xRg = NothingSet xDependRg = NothingApplication.EnableEvents = TrueEnd SubCopy

備注：在代碼中，數(shù)字7表示將先前的單元格保存到的G列，而C：C是將先前的單元格值保存的列。 請(qǐng)根據(jù)您的需要進(jìn)行更改。

3。 點(diǎn)擊 工具 > 參考資料 打開 裁判– VBAProject 對(duì)話框，檢查 Microsoft腳本運(yùn)行時(shí) 框，最后單擊 OK 按鈕。 看截圖：

4。 按 其他 + Q 關(guān)閉鍵 Microsoft Visual Basic應(yīng)用程序 窗口。

從現(xiàn)在開始，當(dāng)C列中的單元格值更新時(shí)，該單元格的先前值將保存到G列中的相應(yīng)單元格中，或保存在注釋中，如下面的屏幕快照所示。

將以前的單元格值保存在其他單元格中：

在注釋中保存以前的單元格值：

最佳辦公生產(chǎn)力工具

將小時(shí)轉(zhuǎn)化為分鐘 Kutools for Excel!

準(zhǔn)備好增強(qiáng)您的 Excel 任務(wù)了嗎？ 利用的力量 Kutools for Excel - 您終極的節(jié)省時(shí)間的工具。 簡(jiǎn)化復(fù)雜的任務(wù)并像專業(yè)人士一樣瀏覽數(shù)據(jù)。 以閃電般的速度體驗(yàn) Excel！

為什么需要 Kutools for Excel