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

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Firefox en Safari!

Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

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.

Lees verder... Gratis download... Aankoop...

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!

Lees verder... Gratis download... Aankoop...

38 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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 ?
- Reply
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
- Reply
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
- Reply
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?
- Reply
- 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
  
  Reply
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.
- Reply
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.
- Reply
- 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
  
  Reply
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
- Reply
- 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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Amir Aizat Bin Alias · 1 years ago
  
  doesn't work for formulas
  
  Reply
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.
- Reply
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 ?
- Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Good day,
  Which Excel version do you use?
  
  Reply
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
- Reply
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!
- Reply
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 ?
- Reply
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!
- Reply
- 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.
  
  Reply
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
- Reply
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
- Reply
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
- Reply
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?
- Reply
- 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.
  
  Reply
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?
- Reply
- 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
  
  Reply
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?
- Reply
- 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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Elsa · 10 months ago
  
  Can the records be in Horizontal instead of Vertical?
  
  Reply
  
  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!
  
  Reply
  
  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
  
  Reply
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.
- Reply
- 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.
  
  Reply
  
  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
  
  Reply
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  CZR · 1 years ago
  
  CAN WE GET THE VBS FOR THE ABOVE
  
  Reply
  
  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
  
  Reply

Log in op uw account

Wachtwoord Resetten