VBA help needed remembering previous values of a cell

[mortoncarl]

Hi ive searched everywhere even tried these paid excel help sites but still no luck.
i have live data coming into cell bj5 the values are constantly changing e.g 10....9...12....10...11 and so on
how do i remember the previous 3 or 4 values of the cell and display them in say bk5 bk6 bk7 bk8 so i can use a formula to get the average value
the values are only been displayed in bj5 though not above or blow it which seems to be an issue with these sites
Many thanks hopefully someone smart out there can help...5 days of trying to figure this out

[Glenn Kennedy]

You'll need VBA for that. If that's OK for you, I will move this thread to the VBA sub-forum.

[mortoncarl]

yes thaats fine

[AliGW]

Thread moved.

[KOKOSEK]

So, in some way you will getting a value which is put into BJ5 but before that you want to keep 'actual value' in range.
Let try something like this:

Please Login or Register  to view this content.

Check attached file.

[maniacb]

Try this module in the sheet code. Every time the value changes, the old value will be recorded, and so on. To get the average, you'll only need to enter an average function in another cell, ie =AVERAGE(BK5:BK8)

Please Login or Register  to view this content.

[mortoncarl]

Thank you so so very much works great wow carnt thank you enough

[k1dr0ck]

@maniacb how would you ignore a change when the value has not actually changed
example when editing cell BJ5 then not actually changing the value then pressing enter would trigger the change event
how would you ignore that event?

[maniacb]

If you feel confident with VBA, One approach could be to turn off events with this code:

Please Login or Register  to view this content.

And then turn it back on with:

Please Login or Register  to view this content.

[KOKOSEK]

Or:

Please Login or Register  to view this content.

because it means that last recorded value has not changed.

[k1dr0ck]

i see, thanks