Recording/Logging Cell Value When Value Changes

[bdb1974]

Hi all, this has been stumping me for awhile.

Is there a way to copy/transfer a cell value to an adjacent cell when the value of the cell changes. So if A1= 5, and it changes to 3, have
B2 = 5 when this happens.

Thanks.

bdb

[mudraker]

It can be done with a Woksheet Change event macro

Is A1 the only cell that this is to occur for?

[bdb1974]

No actually it will be for about 863 or so cells. Not all cells will have value changes. I don't know if it would be better (more efficient) to transfer all data to a second column regardless if a change occurs, or just have it so
data transfers for each cell that actually has a change. I think I'd rather have it to where only the cells having a change in value gets moved over. I maybe getting ahead of myself, but, it would also be nice if the next column could
log the date of the changed event for the cell to the left. Anyway, it will be a huge step for me if I just get the first
part working.

- bdb

[mudraker]

The code can add the date the cell was changed

can you give examples of the range of cells you want to track the changes

[royUK]

maybe you could store the previous value in a cell comment, unless you need it in other calculations

[bdb1974]

Mudraker, Here's an example sheet.

The range would be B6:B867, When A occurs to a cell within that range, the current value of that cell will me to column D, and the date of change will be
in column E for the repective row.

I'd like to be able to track the last few consecutive changes to an cell value.
So, I created another group of columns to the right. So, maybe when D changes a 2nd time, it's value along with the respective date can be copied
to I and J. Plus I'd like to copy the values and dates from columns F&G to K&L. I've got a macro I'm planning on placing on the page that will upadte the date in G for an entry in F.

Roy UK, If I understand you correctly,that would be alot of manual labor to store each value in a comment and then have to move the mouse cusor over
each cell to view the last value. I need this to be automated as much as possible.

Hope this makes sense.

Thanks,

bdb

[royUK]

The comment could be added/updated using code when the cell changes

[bdb1974]

Can you or Mudraker please post an example for the change of event macro. I do not know much about writing VBA code.

Thanks,

bdb

[mudraker]

this example is not built for our situation
It places date in column b when a1 to a8 changes

I don't have the time to look at your problem in detail at the moment as it will need to take into account removing the oldest tracked data when it gets over the number of changes you are tracking, moving the kept changes to the left before adding the latest change

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy the macro
'GoTo Excel
'Select sheet this macro is to apply to
'Right Click on Sheet Name Tab > select View Code
'Paste macro into the Worksheet Module displayed

Please Login or Register  to view this content.

[bdb1974]

Thanks for the posting for the insertion of the date. But, my main problem
is getting the values within the range transferred to another column before
any changes occurs to them.

bdb

[royUK]

Can I ask why you need to do this?

[bdb1974]

Several reasons. I'm tracking and comparing records of quantities of the same item from two sources. The first source for quantity is from what's in inventory according to data entered into the purchasing inventory software database. The second source physically counts quantities to ensure system count is correct. Sometime theres quantities on hand that does not ever get placed back into the system. With my excel program, I'm able to bring in the system's inventory quantity. Each time this information is updated and there is a change in the inventories record, I have no way to update the physical yard count to match the ongoing change or difference in quantity count. By knowing the difference/change, I can use the difference in count and apply it to the physical count to keep it accurate. Also, knowing the difference each time there's a change, I'll be able to track the activitiy of how often items are being used/charged out and the quantity's being used each time. I'll better able to assess my needs and usage of materials if I know the history of our materials activity.


Thanks,

bdb

[royUK]

This code will record the previous value in Column B in Column D, adding the date to E.

Please Login or Register  to view this content.

I'm not clear where else you want to save changes to

[bdb1974]

Roy, Thanks!

I had to make a few changes to get the values from B to transfer to D.
without iterations/looping. But the change I made seems to have fixed
the problem. I tried adding a bit more code to get F to transfer to K, so
far it's not working.

Here's the current code:

Please Login or Register  to view this content.

Do I need:

Please Login or Register  to view this content.

...being that more than 1 row may have a change in value?

[royUK]

Not sure what looping you needed. This amendment will record changes in Column B to In D, changes in Column F to K

Please Login or Register  to view this content.

[bdb1974]

Is There any reason why this will not work in Excel. 2003 properly?

I can't seem to get it function here at work.

Thanks,

bdb

[royUK]

It was written in Excel 2003

[bdb1974]

I'm posting an example of what I'm trying to do.
The change of events will not work with a input from a formula.
It has to happen by manual input / vba code. I've got code to try to
bring it qty's from a sheet named "Inventory_GUS" column "Y".

So for, I'm not able to get it to work.
Here's the code:

Please Login or Register  to view this content.

End Sub



Any help is appreciated.

Thanks,

bdb

[bdb1974]

Roy UK and Mudraker,

Thanks Mudraker for letting me know it can happen.

Thanks RoyUK for writing some code. What you have provided does work in itself. The only problem I have is getting now getting the initial values into
the page by some other means (meaning VBA). Manual entering of values is not an option because I have to many. As I think I have read and also
have experienced, a "Change of Events" macro/formula will not work when the actioning value in brought into a cell by another formula. I am hoping to
be able to work this problem out. If not, I will hope that I can get some
more experienced help here to figure how to ultimately get it to work with my workbook program.

Thank again guys,

bdb

[Gertheking]

The last code is nearly perfect for what I need except I am not manually inputting the data, it is being calculated as the result of a formula. I tried changing the the first line to;

Private Sub Worksheet_Calculate() (I took this from another post)

...but it didn't work. You may have already guessed I am a complete...lets say novice

Any help would be much appreciated.

Matt

[arlu1201]

Matt,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.