Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Como registrar a mudança de valores em uma célula do Excel?

Como registrar cada valor em mudança para uma célula que muda freqüentemente no Excel? Por exemplo, o valor original na célula C2 é 100, ao alterar o número 100 para 200, o valor original 100 será exibido na célula D2 automaticamente para gravação. Vá em frente para alterar 200 para 300, o número 200 será inserido na célula D3, alterar 300 para 400 exibirá 300 para D4 e assim por diante. O método neste artigo pode ajudá-lo a alcançá-lo.

Registre os valores alterados em uma célula com o código VBA


Registre os valores alterados em uma célula com o código VBA


O código VBA a seguir pode ajudá-lo a registrar todos os valores alterados em uma célula no Excel. Faça o seguinte.

1. Na planilha que contém a célula que você deseja registrar, clique com o botão direito na guia da planilha e clique em Ver código no menu de contexto. Veja a imagem:

2. Então o Microsoft Visual Basic para Aplicações a janela está se abrindo, copie o código VBA abaixo para a janela de código.

Código VBA: registra os valores alterados em uma célula

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

Notas: No código, C2 é a célula na qual você deseja registrar todos os seus valores alterados. D2 é a célula em que você preencherá o primeiro valor de alteração de C2.

3. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

A partir de agora, toda vez que você alterar valores na célula C2, os valores de alteração anteriores serão registrados em D2 e ​​as células abaixo de D2.


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
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