Forum Discussion

Aless1275's avatar
Aless1275
Copper Contributor
Oct 06, 2021
Solved

Create a MACRO to save previous values of a changed Row in new ones (creating a database in Excel)

Hi,

I'm here today to solve a problem that is really blocking me, and i would be super greatful if you could help me to solve it!

As you can see in the attached file, i have the following purpose: I want to create a MACRO that i could associate to the "MACRO button", which would allow me when i press it to save in the Database sheet the new row that i created. But the "Database" page should keep the data of the previous rows that he saved by inserting the new ones on top, without overwriting the old ones but just pushing them in the lower rows. The values saved in the Database would then create new rows with the old data everytime i push the button in "Input". I found a code online that keeps track of previous data obtained, which is what i want to do, but 1) it does it with columns instead of rows and 2)it keeps track only of the last result, without creating new columns (in my case rows) to mantain memory of also the previous data. I will post it down to let you see what i mean, but ofc i would be open to both a new code or hints on how to adapt this one to my needs. Thanks a lot in advance! I will just disturb you mtarler since i saw that you are a magician of Excel, solving a lot of threads

 

Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xCell As Range
Dim xDCell As Range
Dim xHeader As String
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = "Previous value :"
x = xDic.Keys
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
Set xDCell = Cells(xCell.Row, 4)
xDCell.Value = ""
xDCell.Value = xDic.Items(I)
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range("G:G"))
End If
Label1:
Set xRg = Intersect(Target, Range("G:G"))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Formula
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End Sub

 

  • Aless1275  OK, I'm not sure the bigger context of what you're doing and think other solutions may work for you.  Second, I ignored the code you sent as you said it doesn't do what you want.  So in the attached I added a very small macro that will copy the row from "Inputs" to "Database".  While I was at it, since it appears you want this as some sort of audit trail I also added timestamp and user to that record and also made it a table so you can easily sort or filter those records.  I hope this helps but if I knew more about the context I might suggest some other solution(s).  For example your insistence of the newest value on top is trivial to do but also easily performed using sorting of the table (at least now that I added the timestamp).  Well, best of luck.

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor

    Aless1275  OK, I'm not sure the bigger context of what you're doing and think other solutions may work for you.  Second, I ignored the code you sent as you said it doesn't do what you want.  So in the attached I added a very small macro that will copy the row from "Inputs" to "Database".  While I was at it, since it appears you want this as some sort of audit trail I also added timestamp and user to that record and also made it a table so you can easily sort or filter those records.  I hope this helps but if I knew more about the context I might suggest some other solution(s).  For example your insistence of the newest value on top is trivial to do but also easily performed using sorting of the table (at least now that I added the timestamp).  Well, best of luck.

    • Aless1275's avatar
      Aless1275
      Copper Contributor
      Thank you very much for your help!!!
      I will now try to implement it in the model. To give you a better picture of what I need, I'm a student working on an economic model for a Renewable-energy power plant. In this model there are some parameters as input that can be modified (in the input sheet), like the size of the plant, its type etc... These values are used in other sheets that i made which give as a result some economic parameters. So, to see how these results change depending on the input variables, i needed some way to store the results after performing the change in the input, without having it overwritten. This to then create tables/graphs and analyse which impact these input variables have on the economic feasibility of the project. If you think there is a better way to do it i'm totally open to learn more from you! Thanks a lot again
      • mtarler's avatar
        mtarler
        Silver Contributor
        well it sounds like you are doing a hunt and peck sort of operation trying to find good/bad values. I would suggest you create a table in which each column is calculated based on previous columns (i.e. instead of your formulas being in 1 cell they are in a column in your table) and then you can enter a whole column of numbers or a series.
        Alternatively with the new dynamic array capabilities of Excel you can have a single formula work on a column of numbers and return an array or results (e..g. =A1:A20 + 5 will return an array of numbers/cells that are exactly 5 more than the cells in column A using only 1 formula in cell B1 for example)
        That all said if your formula and calculation are very complicated and require significant processing then maybe it is better to not tax excel using so may spreadsheet formulas but then maybe you should consider having VBA actually calculate all the values and paste then in.
        Hope that helps and best of luck with that project.

Resources