Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe veranderende waarden in een cel in Excel vastleggen?

Hoe elke veranderende waarde vast te leggen voor een regelmatig veranderende cel in Excel? De oorspronkelijke waarde in cel C2 is bijvoorbeeld 100, wanneer het getal 100 in 200 wordt gewijzigd, wordt de oorspronkelijke waarde 100 automatisch in cel D2 weergegeven voor opname. Ga je gang en verander 200 in 300, nummer 200 wordt ingevoegd in cel D3, verander 300 in 400 geeft 300 in D4 weer, enzovoort. De methode in dit artikel kan u daarbij helpen.

Registreer veranderende waarden in een cel met VBA-code


Registreer veranderende waarden in een cel met VBA-code


De onderstaande VBA-code kan u helpen elke veranderende waarde in een cel in Excel vast te leggen. Ga als volgt te werk.

1. In het werkblad bevat de cel waarvan u de veranderende waarden wilt vastleggen, klik met de rechtermuisknop op de bladtab en klik vervolgens op Bekijk code vanuit het contextmenu. Zie screenshot:

2. Vervolgens de Microsoft Visual Basic voor toepassingen venster wordt geopend, kopieer de onderstaande VBA-code naar het codevenster.

VBA-code: registreer veranderende waarden in een cel

Dim xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("C2").Value Then
         Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
End Sub

Notes: In de code is C2 de cel waarvan u alle veranderende waarden wilt opnemen. D2 is de cel waarin u de eerste veranderende waarde van C2 zult invullen.

3. druk de anders + Q toetsen om de Microsoft Visual Basic voor toepassingen venster.

Vanaf nu worden elke keer dat u waarden in cel C2 wijzigt, de vorige veranderende waarden vastgelegd in D2 en de cellen onder D2.


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
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.
    Tony0928 · 2 months ago
    Hello , I try to use this code to download changing data from web (there is a existing excel sheet to collect data from web automatically ), but , it doesn't work to record data change history record . Any reason about that ?

  • To post as a guest, your comment is unpublished.
    MikeC · 8 months ago
    Hi, Thanks for the below. Quick question....are you able to reset this at times so that on your request, you can get the macro to delete all previous numbers and start recording numbers again from cell D2? At the moment, numbers are recorded D2, D3, D4, D5, D6 etc
  • To post as a guest, your comment is unpublished.
    Juan · 9 months ago
    Hello! I tried using this code to record every change in the value of a particular cell. However, I was wondering if anyone could help me by modifying it so the change in value is collected in a DIFFERENT tab and also so it is saved every time the workbook is closed. Since it sort of re-sets itself each time the workbook is opened without saving the previous values.
    Code:
    Dim xVal As String
    'Update by Extendoffice 2018/8/22
    Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("J7").Address Then
    Range("AB2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    Else
    If xVal <> Range("J7").Value Then
    Range("AB2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    End If
    End If
    Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("J7").Value
    End Sub
  • To post as a guest, your comment is unpublished.
    John · 1 years ago
    Can this be changed to work for multiple cells in one worksheet?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Please try the method in this article:
      How to remember or save previous cell value of a changed cell in Excel?
      https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
  • To post as a guest, your comment is unpublished.
    Hugo · 1 years ago
    Is it possible to adapt this and use with DDE/RTD? Works fine when manually changing the cells, but not with DDE/RTD.
  • To post as a guest, your comment is unpublished.
    Raymond Ramirez · 1 years ago
    This world fine, however, I ned to apply this code to 2 different cells, saving the changing values for each cell in separate, corresponding columns. How can the code be modified? Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Please try the method in this article:
      How to remember or save previous cell value of a changed cell in Excel?
      https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
  • To post as a guest, your comment is unpublished.
    Tom c · 1 years ago
    The formula above doesn't work for formulas, only for manual input. is there any way to change the coding to make it work for cells which contain formula?



    Dim xVal As String
    'Update by Extendoffice 2018/8/22
    Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
    Range("D2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    Else
    If xVal <> Range("C2").Value Then
    Range("D2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    End If
    End If
    Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Please try the below VBA.

      Dim xVal As String
      Private Sub Worksheet_Change(ByVal Target As Range)
      Static xCount As Integer
      Application.EnableEvents = False
      If Target.Address = Range("C2").Address Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      Else
      If xVal <> Range("C2").Value Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      End If
      End If
      Application.EnableEvents = True
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      xVal = Range("C2").Value
      End Sub
  • To post as a guest, your comment is unpublished.
    sam dan · 1 years ago
    Thanks very much for the tutorials, I like to know if there is a way to make just a cell behave as a normal calculator.
    i.e this cell should be capable of summing figures that appears in another cell, while keeping last cumulative figure visible.
    This other cell will be the key-in cell or active cell.
    Example:
    Cell 1: =2*5, Answer appears in Cell 2,
    Cell 1: =3*6.8, Answer is added to the previous value resulting from (2*5) and still appears in cell 2.
  • To post as a guest, your comment is unpublished.
    marcin · 2 years ago
    it works when I type in the data, it doesn't work when I stream real time data to this cell directly (=RTD(.....)). how can I make it work with RTD ?
  • To post as a guest, your comment is unpublished.
    krishna · 2 years ago
    I tried this code for the C2 cell which contatins DDE values which changes second by second. I use this following code but not working.

    Dim xVal As String
    Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
    Range("D2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    Else
    If xVal <> Range("C2").Value Then
    Range("D2").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    End If
    End If
    Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Which Excel version do you use?
  • To post as a guest, your comment is unpublished.
    Marky Mark · 2 years ago
    Try This

    Dim xVal As String
    Dim iVal As Integer
    Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    iVal = Application.WorksheetFunction.Count(Range("F:F"), 1)
    xCount = iVal
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
    Range("E3").Offset(xCount, 0).Value = Range("C2").Value
    Range("F3").Offset(xCount, 0).Value = Now
    xCount = xCount + 1
    Else
    If xVal <> Range("C2").Value Then
    Range("E3").Offset(xCount, 0).Value = Range("C2").Value
    Range("F3").Offset(xCount, 0).Value = Now
    xCount = xCount + 1
    End If
    End If
    Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
    End Sub
  • To post as a guest, your comment is unpublished.
    Jackie · 2 years ago
    Hi! Thanks for the code, but I have a question

    Is there a way to modify the code, such that it records the value if the cell daily, or on certain days, which I specify in a separate column?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Anthony · 2 years ago
    Hello, Would it be possible to apply this for more than one cell ?
  • To post as a guest, your comment is unpublished.
    JL007 · 2 years ago
    How do I save the number as soon as it is generated not after a new number is generated? The problem I am having is the number is not recorded right away but after a second number is created; this means neither the cell that I am recording or the cell that is the target have the number...how can I record as soon as the number is generated? Thanks for your help!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi John,
      Sorry can't help you with that. Welcome to post any question in our forum: https://www.extendoffice.com/forum.html. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Yusuf · 2 years ago
    Peki bu kaydı yatay olarak nasıl kaydedeceğiz. Satırlara değil Sütunlara kaydetmesini istiyorum. Teşekkürler
  • To post as a guest, your comment is unpublished.
    Abdallah · 2 years ago
    Thanks

    but what can i do if i need to repeat it for a raw
  • To post as a guest, your comment is unpublished.
    Abdallah · 2 years ago
    Thanks

    but what can i do if i need to repeat it for many cells
  • To post as a guest, your comment is unpublished.
    wayne · 2 years ago
    thank you for this but instead of going on forever how could i restart back at first cell after X amount of times?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Do you mean after recording X mount of times, you want to restart back to the first record value?
      Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do.
  • To post as a guest, your comment is unpublished.
    Maybe · 3 years ago
    Hello, Would it be possible for this macro to record two seperate cells in two seperate columns? Ie. Can I record All values from A1 in Column B and all Values of C1 in column D?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi,
      Please try the method in this article:
      How to remember or save previous cell value of a changed cell in Excel?
      https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
  • To post as a guest, your comment is unpublished.
    Xy · 3 years ago
    What if cell C2 is a formula? How do I record the values of C2 if it is a formula?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      The code has been optimized. Please have a try and thanks for your comment.

      Dim xVal As String
      Private Sub Worksheet_Change(ByVal Target As Range)
      Static xCount As Integer
      Application.EnableEvents = False
      If Target.Address = Range("C2").Address Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      Else
      If xVal <> Range("C2").Value Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      End If
      End If
      Application.EnableEvents = True
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      xVal = Range("C2").Value
      End Sub
      • To post as a guest, your comment is unpublished.
        Elsa · 10 months ago
        Can the records be in Horizontal instead of Vertical?
      • To post as a guest, your comment is unpublished.
        Deana Zabaldo · 1 years ago
        Hi--I really appreciate this tutorial...I'm trying to record the changed value on a different spreadsheet. For example, I want to record the value of sheet1 C2 on sheet2 D2. Can you provide adjusted code?

        Thank you!
      • To post as a guest, your comment is unpublished.
        alex · 1 years ago
        HAI ,

        THE ABOVE VBS ONLY C2 MOVE TO D2,

        INEED C2 TO C55 MOVE TO D2 TO D55

        CAN YOU HELP AND SEND TO ME alexmathew33@gmail.com
  • To post as a guest, your comment is unpublished.
    Jorge Jaramillo · 3 years ago
    Hi


    This works really well if the value in C2 is entered each time, but it doesn't work if C2 contains a formula. Is there a way to this same thing but with a formula in C2?


    Thanks for this easy solution.
    • To post as a guest, your comment is unpublished.
      ack1128@gmail.com · 3 years ago
      If you find out how to use it if C2 contains a formula will you please please let me know how you did it. I can't seem to find how to anywhere on the internet.
      • To post as a guest, your comment is unpublished.
        crystal · 3 years ago
        Good Day,
        The code has been optimized. Please have a try and thanks for your comment.

        Dim xVal As String
        Private Sub Worksheet_Change(ByVal Target As Range)
        Static xCount As Integer
        Application.EnableEvents = False
        If Target.Address = Range("C2").Address Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        Else
        If xVal <> Range("C2").Value Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
        End If
        Application.EnableEvents = True
        End Sub
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        xVal = Range("C2").Value
        End Sub
        • To post as a guest, your comment is unpublished.
          alexmathew · 1 years ago


          HAI ,

          THE ABOVE VBS ONLY C2 MOVE TO D2,

          INEED C2 TO C55 MOVE TO D2 TO D55

          CAN YOU HELP AND SEND TO ME alexmathew33@gmail.com
          • To post as a guest, your comment is unpublished.
            CZR · 1 years ago
            CAN WE GET THE VBS FOR THE ABOVE

          • To post as a guest, your comment is unpublished.
            melukota · 1 years ago
            Please help me on the below scenario:
            From Sheet 1:
            A1=VALUE (Changes due to RTD with Formula)
            B2= VALUE1 (Changes due to RTD with Formula)

            Copy all previous values cells A1,B1 into Sheet2 of columns M,NOF Same excel or Sheet1 of New Workbook

            Please share with me to melukotahari@gmail.com

            -Melukota