Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I want a macro that records change in Values in Excel

0

I have data coming from web into my excel that is changing every minute. Wherein i have two cells (C19, D19) whose values to i want to keep a track of.

All  these change in values i want to keep in two columns (L & M) where it is recording all change in values and saving it in those columns (Every change).

Please help me out with a macro based on these Cell names only (would be easier to implement).

Also, C19 & D19 are formula based cells where it is computing a particular value & putting it there.


Please help me out here.

Answer
Discuss

Discussion

How does the data come into Excel and if C19 and D19 are formulas, does the data that comes into Excel get put into other cells? The recording of the data is easy, you just need to provide enough information to figure out how to trigger the saving, either from a macro that imports the data now or from an event.
don (rep: 1989) Jan 28, '21 at 11:53 am
C19 & D19 is simply a ratio calculated by dividing two values in different cells.

just that..

pls share the macro for recording..
sunnyn73 Jan 28, '21 at 12:03 pm
Please read all of my response, particularly this part: "if C19 and D19 are formulas, does the data that comes into Excel get put into other cells?"
don (rep: 1989) Jan 28, '21 at 1:43 pm
C19 & D19 are calculated based on table values already there in the excel.

Those values in table are getting feeded via web source will automaically fetches that table for the web.

so C19 & D19 are being calculated based on actual values present in the table..

Please share further on this now..
sunnyn73 Jan 28, '21 at 1:49 pm
Please refer to this screenshotted sheet here: http://prntscr.com/xv8nbm (This is what exactly my work look like) Here the table is sourced from some other sheet which is imported from the web source, and from that source i have taken the values & doung my calculations based on that data.

Now, as the data in table changes every minute, my values "B18" & "C18" are changing every minute. Formula is simple, i am just calculating the "Change in total OI & Change in change OI" (Formula is also in screenshot)   All i simply want is to automatically save all change in values of B18 & C18, seperately in columns L & M.   That's it... Just that.....nothing else...     
sunnyn73 Jan 30, '21 at 8:12 am
Somewhere in B3:E12 in your screenshot there is a cell that does not contain a formula and whose value is changed every minute by some program that writes to it. In fact, there are probably several or even many cell that match this discription but it will be enough to know one. The problem is, this cell could be anywhere, might even be on another sheet, but it's this cell which is the trigger to all changed calculations on the worksheet, including the formulas in B18:C18. Therefore it also is the trigger for making the record you want in columns L:M. Once you disclose the location of that cell your problem will be solved forthwith.
The formulas in B18:C19 probably refer to the cells that are being changed but that's not sure. They could also refer to cells that contain other formulas. Look for a cell that contains a hard value that comes from the web.
Variatus (rep: 4889) Jan 30, '21 at 7:40 pm
This table B3:E12 is sourced from other sheet where i pulled the data from the web and every minute it is changing. and in that sheet i have made a connection with a web link & simply imported the table from that page (which keeps on chaning on that webpage & i intend to get those changes in my sheet every minute). So in table B3:E12, i have used all cells as "=VALUE(Source1!D41)" (Sourcing all data from other sheet) and my calculations is being done on the table data. Forumulas i have disclosed to you already. Therefore, knowing the record of the change in values of B18,C18 will tell me what kind of change in happening. Thats what i want.     Thats everything there is i can tell, nothing else is there.
sunnyn73 Jan 31, '21 at 2:47 pm
So you need to do what I said in my answer. Change the sheet reference and cell reference to the cell that is updated on your other sheet and then use that to trigger saving values from the cells with the formulas. You can't make a macro run when a formula changes, you must make it run when the original value that the formula references is changed.
don (rep: 1989) Jan 31, '21 at 5:38 pm
Yes, you can make a macro run based on changes in B18:C18. It's possible and not too difficult but it's the VBA equivalent of touching your left ear with your right hand by extending your arm over your head. In real life there are better solutions to the task.
Variatus (rep: 4889) Jan 31, '21 at 7:20 pm
@Don: I would really request you to talk in terms of cell names & formulas. Not so familiar to techincal language here.
sunnyn73 Feb 1, '21 at 4:17 pm
Add to Discussion

Answers

0

Assuming that you are talking about a regular table and not a PivotTable or a Query Table, then you need to run a macro every time a cell is changed on the worksheet with the Table and then storing the values from the desired cells into another worksheet.

Here is a sample macro that runs when the value of cell A1 in the Table is changed and then takes the value that you want to store from the other worksheet and puts it on the next row in a worksheet called Record. Change A1 to the cell that will be updated with the new value in the Table and make sure to add a worksheet to record the value and call it "Record" and then change the worksheet reference Other Worksheet to the name where the formulas are whose output you want to save and then change C2 to the cell that has the formula to save. You can repeat that line of code for as many values as you want to save.

Make sure to put this code inside the code module for the worksheet that contains the Table of data that is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then

        nextRow = Worksheets("Record").Cells(Cells.Count, 1).End(xlUp).Offset(1).Row

        Worksheets("Record").Cells(nextRow, 1).Value = Worksheets("Other Worksheet").Range("C2").Value

    End If

End Sub
Discuss

Discussion

Please refer to this screenshotted sheet here: http://prntscr.com/xv8nbm (This is what exactly my work look like) Here the table is sourced from some other sheet which is imported from the web source, and from that source i have taken the values & doung my calculations based on that data.

Now, as the data in table changes every minute, my values "B18" & "C18" are changing every minute. Formula is simple, i am just calculating the "Change in total OI & Change in change OI" (Formula is also in screenshot)   All i simply want is to automatically save all change in values of B18 & C18, seperately in columns L & M.   That's it... Just that.....nothing else...     
sunnyn73 Jan 30, '21 at 8:13 am
Did you try my answer? If you don't understand a part of it, let me know.
don (rep: 1989) Jan 31, '21 at 5:39 pm
I didn't understand what to put under C2. Suppose, value B18 is changing continously i added that in target range, also made "Record" sheet & "Source sheet" (However, i want it on the same sheet).   Really don't know what to put under C2.  
sunnyn73 Feb 1, '21 at 4:11 pm
Add to Discussion
0

 Here is the procedure that's at the heart of the system I am about to introduce to you.

Sub RecordPriceChange()
    ' 164

    Dim Source      As Variant
    Dim Target      As Range
    Dim C           As Long

    ' change the name of the worksheet to the actual name
    ' of the sheet on which you want the record kept
    With Worksheets("TXL_5067")
        Source = .Range("D18:D19").Value
        Set Target = .Cells(.Rows.Count, "L").End(xlUp).Resize(1, 2)
        LastRecord = Target.Value
        For C = 1 To UBound(Source)
            If Source(C, 1) <> LastRecord(1, C) Then
                Target.Offset(1).Value = Application.Transpose(Source)
            End If
        Next C
    End With
    SetSchedule
End Sub

It needs a lot of support. Please follow my instructions very carefully. You can check what you are doing against the installation in the attached workbook.

The above procedure must be installed in a standard code module, along with the declarations and procedures you find below.

Option Explicit

Dim NextExecution   As Double           ' time of execution
Dim LastRecord      As Variant          ' last data recorded

Sub SetSchedule()
    ' 164

    ' set the time interval: 00:01:00 = 0 hours, 1 minute, 0 seconds
    NextExecution = Now() + TimeValue("00:01:00")
    Application.OnTime NextExecution, "RecordPriceChange"
End Sub

Sub EndSchedule()
    ' 164
    On Error Resume Next
    Application.OnTime NextExecution, "RecordPriceChange", , False
End Sub

Make sure that the declarations are at the top of the code module as you find them in the attached workbook (module TXL_5067). Let the main procedure be at the bottom.

To explain: The procedure SetSchedule sets the time when the Sub RecordPriceChange should be run, for example, 1 minute from NOW(). Excel's Timer will run the procedure after 1 minute.

RecordPriceChange checks the last entry in columns L:M against D18:D19 and does nothing if they are the same. But if one of them was changed it will record the new values. It will then call SetSchedule again and that will repeat the cycle after 1 minute.

This cycle will never stop. To interrupt it you have the procedure EndSchedule. When that procedure is run the cycle will end.

I have installed two methods to automate this system. One is linked to opening and closing the workbook. You will find the code below in the ThisWorkbook code module.

Option Explicit

Private Sub Workbook_Open()
    ' 164
    RecordPriceChange
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 164
    EndSchedule
End Sub

The first procedure runs when the workbook is opened and calls RecordPriceChange. The second one runs before the workbook is closed and stops the cycle.

The second method is controlled by the button on the worksheet. It has the caption "Start recording" and calls RecordPriceChange when clicked. At the same time the caption of the button is changed to "Stop recording" and its function will be to call EndSchedule when clicked next time.

The button uses the code below which must be in the code module of the worksheet on which the button resides (which doesn't have to be the one on which you have the action).

Option Explicit

Private Sub CommandButton1_Click()
    ' 164

    With CommandButton1
        If .Caption = "Start recording" Then
            RecordPriceChange
            .Caption = "Stop recording"
        Else
            .Caption = "Start recording"
            EndSchedule
        End If
    End With
End Sub

I don't think you will keep both systems. Therefore I didn't synchronize the button caption with the system status. If the system was started by the Workbook_Open procedure the button caption will still be "Start recording". But pressing the button to change the caption will do no harm. It will just delay the next execution a few seconds.

Discuss

Discussion

Hi Sunny,
The next step is to download the attached workbook, make a change in D41 of Sheet2 and look at the record made in Sheet1!L:M.
Variatus (rep: 4889) Feb 1, '21 at 9:22 pm
Why there's a need to make a change in some distant cell that has nothing to do with my calculation ? The following code however helps me record without giving trigger to some distant cell....   Can u help me do it for multiple Cells ? (It only does for 1 cell and it works well)    
sunnyn73 Feb 2, '21 at 8:19 am
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
sunnyn73 Feb 2, '21 at 8:19 am
My above code responds to a change in the "distant cell" because it's that change which triggers a change in D18:D18. It's the best solution to your problem.
Your code responds to a change in C2 which is just another "distant cell". The problem is that your code writes the value of C2 (one cell) to D2 (also one cell) but in your request you want to write values of 2 cells to columns L:M (also 2 cells). So, presuming that you want to write C2 to column L, which is the value to write to column M?
However, since your code and mine both need a "distant cell" the easiest way forward would be to adjust my code. Just paste it to the code module of the worksheet on which you have C2 and replace "D41" in the code with "C2". My code would then still write the values from D18:D19 to columns L:M, assuming that the change in C2 will trigger changes in D18:D19.
Variatus (rep: 4889) Feb 2, '21 at 10:31 am
Hey, i tried your code. I used a constantly changing value as a trigger. Everything seems to work fine only if i manually change the values. The trigger C2, is changing every one minute, that that value their is sourced from web & called in a table. Even though i am using =value(source"sheetname") still its not changing along with the change in trigger value.
sunnyn73 Feb 8, '21 at 1:12 pm
so i am back to square one.
sunnyn73 Feb 8, '21 at 1:13 pm
Yes, it would seem so. What appears to be happening is that the program that changes C2 somehow prevents the change event from occurring. I'll find another solution for you. Give me a day or two.
Variatus (rep: 4889) Feb 8, '21 at 7:00 pm
I have postged a revised answer that focuses on D18:D19. Please check it out.
Variatus (rep: 4889) Feb 9, '21 at 9:30 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login