Tracking Price Changes

[JLouis]

I've searched the board and got close to a solution but not quite...

I have a WS with items and their associated prices:
small sub 3.99 in 2 adjacent cells

If I change the price to 4.99 I'm looking to record the changes onto a another Log Sheet with the entry
item old new
small sub 3.99 4.99

I've found this so far:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strAddress As String
Dim val
Dim dtmTime As Date
Dim Rw As Long

dtmTime = Now()
val = Target.Value
strAddress = Target.Address

Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
With Sheets("Log Sheet")
.Cells(Rw, 1) = strAddress
.Cells(Rw, 2) = val
.Cells(Rw, 3) = dtmTime
End With
End Sub

Thanks in advance for any help.

Louis

 

[Dan_W]

What is it about your code that doesn't meet your needs?

 

[JLouis]

It doesn't return the value of the product name nor does it give me the old price.

If I change a price now it enters this into the Log Sheet:

ADDRESS	NEW VALUE	TIMESTAMP
$F$2	8.09​	4/27/2020 11:57​

I need it to be this:

Item	Old Price	New Price
small sub	7.99​	8.09​

 

[Dan_W]

Your code is only retrieving pieces of information:

1. dtmTime = Now() -- this captures the time that the code is executed
2. val = Target.Value -- this captures the value (or the contents) of the cell you have made the change in after the change has been made.
3. strAddress = Target.Address -- this captures the address (row/column) of the cell that you made the change into.

If you want to get the item description, I would recommend looking at the OFFSET property. That will allow you look into cells relative to the target cell (i.e., the one you've just made the change in). So in you case, you would want to work out how many cell rows and columns the item information is in relative to the cell you making the change in. If it is adjacent, as you say, then that should be fairly straight forward.

Once you've managed to get the item description from the adjacent cell, you will need to decide what you want to put on the spreadsheet. You currently have:

.Cells(Rw, 1) = strAddress
.Cells(Rw, 2) = val
.Cells(Rw, 3) = dtmTime

From you table above, it seems that you would want to replace the variable in the first column (strAddress) with whatever variable you choose to capture the item description with. If you want Column 3 to have the new price, then you should move that variable from column 2.

In terms of getting the old price, that's a slightly tricker. The code above is triggered once the contents of the have changed - not before then. Meaning the data is gone by the time you want to record the details. That being the case, you need to find somewhere to store that data before you even go to change it. There are number of ways of doing it, but the general approach is to save/make a copy of the new data somewhere - for example, in a hidden column on the same row in the same sheet. That way, when you change the contents of the target cell, your previously 'new' data in the hidden column would then become the old data, and then it is just a matter of retrieving it from the hidden column and, looking your table, putting it in column 2. That's one option, but there are alternatives - I would suggest searching on this forum and other forums/search engines with something along the lines of "worksheet change event previous value old value" and see how you go. Here's some code that might help - link.

 

[JLouis]

Hi Dan. Thank you so much for the reply and solutions. I'm looking forward to tinkering with my book shortly.

 

[Dan_W]

Let us know how it goes. I'd be interested to hear how you choose to solve the issue of storing the 'old' value.

 

[JLouis]

Sure will. The link you provided was successfully tested, so it's just a matter of fitting everything to my application. This is why I love this site. Learn a lot while getting the help you need.

Thanks again.

 

[Dan_W]

That's great. Let me know if you encounter any problems along the way.