提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
注册账户
x

or

如何计算单元格在Excel中更改的次数?

要计算在Excel中更改单元格的次数,本文可以帮助您。

用VBA代码计算单元格更改的次数

使您能够更智能,更快速,更好地完成工作

Kutools - 最好的办公生产力工具为300带来了高级功能。 细节... 下载...

Office选项卡 - 为Excel和Word提供选项卡式界面,使您的工作更轻松。 细节...下载...


用VBA代码计算单元格更改的次数


请按照以下步骤计算单元格在Excel中更改的次数。

1。 在工作表中,您需要统计指定单元格的全部更改,右键单击工作表选项卡,然后单击 查看代码 从上下文菜单。 看截图:

2。 在开幕式上 Microsoft Visual Basic for Applications 窗口,请复制并粘贴下面的VBA代码到代码窗口。

VBA代码:计算单元格在Excel中更改的次数

Dim xCount As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If Target = Range("B9") Then
        xCount = xCount + 1
        Range("C9").Value = xCount                                     
    End If
    Application.EnableEvents = False
    Set xRg = Application.Intersect(Target.Dependents, Me.Range("B9"))
    If Not xRg Is Nothing Then
        xCount = xCount + 1
        Range("C9").Value = xCount
    End If
    Application.EnableEvents = True
End Sub

注意:在代码中,B9是您需要对其更改进行计数的单元格,C9是用于填充计数结果的单元格。 请根据需要更改它们。

从现在起,当单元格B9发生更改时,其更改的总数将被重叠并自动填充到单元格C9中。



Excel生产力工具

Kutools for Excel - 最佳办公生产力工具

  • 为300带来强大的高级功能,使您能够更智能,更快速,更好地完成任务。
  • 不再需要记忆公式和VBA代码,从现在起让你的大脑休息一下。
  • 成为3分钟的Excel专家,复杂和重复的操作可以在几秒钟内完成,
  • 每天减少成千上万的键盘和鼠标操作,现在告别职业病。
  • 110,000高效人才和300 +世界知名公司的选择。
  • 60-day full功能免费试用。 60天退款保证。 2多年的免费升级和支持。

Office选项卡 - 为Office提供选项卡式界面,使您的工作更轻松。

  • 在Word,Excel,PowerPoint,Publisher,Access,Visio和Project中启用选项卡式编辑和阅读。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    JediTrader · 2 months ago
    Team,

    When I tried using :

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range, xCell As Range
    Dim xSRg, xRRg As Range
    Dim xFNum As Long

    Set xSRg = Range("B9:B1000")
    Set xRRg = Range("C9:C1000")

    carefully changing the Range and Target cells vis a vis P2:P200 and X2:X200 respectively, I dont the change-count in X Column despite myself trying to change cells across multiple rows across P2:P200.

    Any help would be greatly appreciated.

    Regards
    JT
  • To post as a guest, your comment is unpublished.
    Shiju · 4 months ago
    Hello All,

    The solution as provided under "Count Number Of Times A Cell Is Changed With VBA Code" is good if we are only tracking changes to ONE CELL. Please suggest, what modifications are needed, if the tracking is to be done for multiple cells. In case of multiple cells, the incremental counter should appear next to the cell for which the change in value is being tracked.
    • To post as a guest, your comment is unpublished.
      Shiju · 4 months ago
      Looking forward for help and assistance to have a specific VBA code, which can be applied to multiple cells in one worksheet.
      • To post as a guest, your comment is unpublished.
        crystal · 4 months ago
        Hi Shiju,
        Please try the below VBA code. Thanks for commenting.

        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim xRg As Range, xCell As Range
        Dim xSRg, xRRg As Range
        Dim xFNum As Long

        Set xSRg = Range("B9:B1000")
        Set xRRg = Range("C9:C1000")

        Application.EnableEvents = False
        On Error Resume Next
        For xFNum = 1 To xSRg.count
        If Target = xSRg.Item(xFNum) Then
        xRRg.Item(xFNum).Value = xRRg.Item(xFNum).Value + 1
        Application.EnableEvents = True
        Exit Sub
        End If
        Next xFNum
        Application.EnableEvents = True
        End Sub
  • To post as a guest, your comment is unpublished.
    Kevin · 1 years ago
    Did you figure this out. I too am interested in this for checking multiple cells. Haven’t tried it yet.
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Hi Kevin,

      The following code can help you solving the problem. Thanks for your comment.
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range, xCell As Range
      Dim xSRg, xRRg As Range
      Dim xFNum As Long

      Set xSRg = Range("B9:B1000")
      Set xRRg = Range("C9:C1000")

      Application.EnableEvents = False
      On Error Resume Next
      For xFNum = 1 To xSRg.count
      If Target = xSRg.Item(xFNum) Then
      xRRg.Item(xFNum).Value = xRRg.Item(xFNum).Value + 1
      Application.EnableEvents = True
      Exit Sub
      End If
      Next xFNum
      Application.EnableEvents = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Jan · 1 years ago
    Thank you very much ! This works great.

    But how do you get the same function/rule to work for a range of cells, along an entire column, for example?

    I have a list of my business' contacts on different rows, with their contact details in different columns, and I want a add a column that registers and counts the number of times a given cell along each row is changed. The code you gave works great, but only for one cell at a time !

    I'm new to VBA, so I would greatly appreciate your support.

    I tried adding a range of cells into the code, so instead of "B9" and "C9", as given in the example above, I played around with variations like "B:B", "C:C", or "B9:B1000" and "C9:C1000", without any success.

    Thanks in advance,
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Hi Jan,
      Please try the below VBA code. Hope it can help. Thanks for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range, xCell As Range
      Dim xSRg, xRRg As Range
      Dim xFNum As Long

      Set xSRg = Range("B9:B1000")
      Set xRRg = Range("C9:C1000")

      Application.EnableEvents = False
      On Error Resume Next
      For xFNum = 1 To xSRg.count
      If Target = xSRg.Item(xFNum) Then
      xRRg.Item(xFNum).Value = xRRg.Item(xFNum).Value + 1
      Application.EnableEvents = True
      Exit Sub
      End If
      Next xFNum
      Application.EnableEvents = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Jonathan · 10 months ago
        Hi Crystal,

        This is brilliant, on my matrix I have used this on one of the columns but I have struggled to duplicate this across multiple columns. Do you have a solution?

        Thanks in Advance