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

网友投稿 911 2024-01-12

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

通常，当用新内容更新单元格时，会覆盖以前的值，除非在 Excel 中撤消操作。但是，如果您想保留先前的值以与更新的值进行比较，将先前的单元格值保存到另一个单元格或单元格注释中将是一个不错的选择。本文中的方法将帮助您实现它。

在Excel中使用VBA代码保存以前的单元格值

惊人的！在 Excel 中使用高效的选项卡，如 Chrome、Firefox 和 Safari！ 每天节省50％的时间，并减少数千次鼠标单击！

假设您有一个表格，如下所示。如果C列中的任何单元格发生更改，则要将其先前的值保存到G列的相应单元格中，或自动保存在注释中。请按照以下步骤进行操作。

1.在工作表中包含更新时将保存的值，右键单击工作表选项卡，然后选择 查看代码 从右键单击菜单中。看截图：

2.在开幕 Microsoft Visual Basic应用程序 窗口，将下面的VBA代码复制到“代码”窗口中。

以下VBA代码可帮助您将指定列的先前单元格值保存到另一列中。

VBA代码：将先前的单元格值保存到另一个列单元格中

Dim xRg As Range Dim xChangeRg As Range Dim xDependRg As Range Dim xDic As New Dictionary Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long Dim xCell As Range Dim xDCell As Range Dim xHeader As String Dim xCommText As String On Error Resume Next Application.ScreenUpdating = False Application.EnableEvents = False xHeader = "Previous value :" x = xDic.Keys For 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) Next Application.EnableEvents = True Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim I, J As Long Dim xRgArea As Range On Error GoTo Label1 If Target.Count > 1 Then Exit Sub Application.EnableEvents = False Set xDependRg = Target.Dependents If xDependRg Is Nothing Then GoTo Label1 If Not xDependRg Is Nothing Then Set xDependRg = Intersect(xDependRg, Range("C:C")) End If Label1: Set xRg = Intersect(Target, Range("C:C")) If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = Union(xRg, xDependRg) ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = xDependRg ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then Set xChangeRg = xRg Else Application.EnableEvents = True Exit Sub End If xDic.RemoveAll For I = 1 To xChangeRg.Areas.Count Set xRgArea = xChangeRg.Areas(I) For J = 1 To xRgArea.Count xDic.Add xRgArea(J).Address, xRgArea(J).Formula Next Next Set xChangeRg = Nothing Set xRg = Nothing Set xDependRg = Nothing Application.EnableEvents = True End Sub

Copy

要在注释中保存以前的单元格值，请应用以下VBA代码

VBA代码：在注释中保存以前的单元格值

Dim xRg As Range Dim xChangeRg As Range Dim xDependRg As Range Dim xDic As New Dictionary Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long Dim xCell As Range Dim xHeader As String Dim xCommText As String On Error Resume Next Application.ScreenUpdating = False Application.EnableEvents = False xHeader = "Previous value :" For I = 0 To UBound(xDic.Keys) Set xCell = Range(xDic.Keys(I)) If Not xCell.Comment Is Nothing Then xCell.Comment.Delete With xCell .AddComment .Comment.Visible = False .Comment.Text xHeader & vbCrLf & xDic.Items(I) End With Next Application.EnableEvents = True Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim I, J As Long Dim xRgArea As Range On Error GoTo Label1 If Target.Count > 1 Then Exit Sub Application.EnableEvents = False Set xDependRg = Target.Dependents If xDependRg Is Nothing Then GoTo Label1 If Not xDependRg Is Nothing Then Set xDependRg = Intersect(xDependRg, Range("C:C")) End If Label1: Set xRg = Intersect(Target, Range("C:C")) If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = Union(xRg, xDependRg) ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = xDependRg ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then Set xChangeRg = xRg Else Application.EnableEvents = True Exit Sub End If xDic.RemoveAll For I = 1 To xChangeRg.Areas.Count Set xRgArea = xChangeRg.Areas(I) For J = 1 To xRgArea.Count xDic.Add xRgArea(J).Address, xRgArea(J).Text Next Next Set xChangeRg = Nothing Set xRg = Nothing Set xDependRg = Nothing Application.EnableEvents = True End Sub

Copy

备注：在代码中，数字7表示将先前的单元格保存到的G列，而C：C是将先前的单元格值保存的列。请根据您的需要进行更改。

3。点击工具 > 参考资料 打开 裁判– VBAProject 对话框，检查 Microsoft脚本运行时 框，最后单击 OK 按钮。看截图：

4。按其他 + Q 关闭键 Microsoft Visual Basic应用程序 窗口。

从现在开始，当C列中的单元格值更新时，该单元格的先前值将保存到G列中的相应单元格中，或保存在注释中，如下面的屏幕快照所示。

将以前的单元格值保存在其他单元格中：

在注释中保存以前的单元格值：

最佳办公生产力工具

将小时转化为分钟 Kutools for Excel!

准备好增强您的 Excel 任务了吗？利用的力量 Kutools for Excel - 您终极的节省时间的工具。简化复杂的任务并像专业人士一样浏览数据。以闪电般的速度体验 Excel！

为什么需要 Kutools for Excel

🛠️ 超过 300 项强大功能: Kutools 包含 300 多项高级功能，可简化您在 1500 多种场景中的工作。

📈 卓越的数据处理能力：合并单元格、删除重复项并执行高级数据转换 - 所有这些都不费吹灰之力！

⏱️ 高效的批量操作：当你可以聪明地工作时，为什么还要付出额外的努力呢？轻松批量导入、导出、组合和调整数据。