Petua: Bahasa lain diterjemahkan Google. Anda boleh melawat English versi pautan ini.
Log masuk
x
or
x
x
Daftar Sekarang
x

or

Bagaimana mengira bilangan kali sel ditukar dalam Excel?

Untuk mengira bilangan kali sel ditukar dalam Excel, artikel ini dapat membantu anda.

Kira bilangan kali sel ditukar dengan kod VBA


Kira bilangan kali sel ditukar dengan kod VBA


Sila lakukan seperti berikut untuk mengira bilangan kali sel ditukar dalam Excel.

1. Dalam lembaran kerja anda perlu mengira jumlah perubahan sel tertentu, klik kanan tab helaian, dan kemudian klik Lihat Kod dari menu konteks. Lihat tangkapan skrin:

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi tetingkap, sila salin dan tampal di bawah kod VBA ke dalam tetingkap Kod.

Kod VBA: Kirakan bilangan kali sel ditukar dalam 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

Nota: Dalam kod, B9 adalah sel yang anda perlukan untuk mengira perubahannya, dan C9 adalah sel untuk mengisi hasil penghitungan. Sila ubah seperti yang anda perlukan.

Mulai sekarang, apabila perubahan dibuat dalam sel B9, jumlah perubahan akan ditumpuk dan diisi sel dalam C9 secara automatik.



Cadangan Alat Produktiviti untuk Excel

Kutools untuk Excel Membantu Anda Selalu Menyelesaikan Kerja di Hadapan Masa, dan Berdiri Daripada Orang

  • Lebih daripada ciri-ciri canggih 300 yang canggih, yang direka untuk senario kerja 1500, meningkatkan produktiviti oleh 70%, memberi anda lebih banyak masa untuk menjaga keluarga dan menikmati kehidupan.
  • Tidak perlu lagi menghafal formula dan kod VBA, berikan rehat dari otak anda sekarang.
  • Menjadi pakar Excel dalam minit 3, Operasi yang rumit dan berulang boleh dilakukan dalam beberapa saat,
  • Mengurangkan beribu-ribu operasi papan kekunci & tetikus setiap hari, mengucapkan selamat tinggal kepada penyakit pekerjaan sekarang.
  • 110,000 orang yang sangat berkesan dan pilihan syarikat 300 + yang terkenal di dunia.
  • Ciri-ciri penuh 60-hari adalah percubaan percuma. Jaminan wang balik 60 hari. Tahun 2 peningkatan dan sokongan percuma.

Membawa Browsing Tab dan Editing ke Microsoft Office, Jauh Lebih Berkuasa Daripada Tab Penyemak Imbas

  • Tab Pejabat direka untuk Aplikasi Pejabat Word, Excel, PowerPoint dan Lain-lain: Penerbit, Akses, Visio dan Projek.
  • Buka dan buat beberapa dokumen dalam tab baharu pada tetingkap yang sama, dan bukannya dalam tetingkap baru.
  • Meningkatkan produktiviti anda oleh 50%, dan mengurangkan beratus-ratus klik tetikus untuk anda setiap hari!
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 · 1 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 · 3 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 · 3 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 · 3 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 · 10 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 · 10 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 · 9 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