how can save previous values when the cells is formula
Hello,
i'm trying use vba to save previous value when the cells is formula.
example:
sheets1 range(B2:B5) have formula to link to sheets2 range(B2:b5)
sheets2 range(B2:b5) is update by another excel files
when update the excel files , sheet1 value is change. how can save the previous value to sheets1 range(c3:c5) and make a tag for which cells has update. thank you
Hi,
The solution I suggest is using a helper range to save the values of formulas. When the sheet calculates, copy the value of helper range to the previous range and then copy the values of formulas to helper range, like this.
PHP Code:Private Sub Worksheet_Calculate()
Me.[C2:C5].Value = Me.[D2:D5].Value
Me.[D2:D5].Value = Me.[B2:B5].Value
End Sub
❖ Please mark your thread is SOLVED if there has been offered a solution that works fine for you.
❖ If you like solutions provided by anyone, feel free to add reputation by clicking on ✶ Add Reputation bottom left of their posts.
Code in Sheet2;
In column D it will put the date and time when it was changed.
Please Login or Register to view this content.
Last edited by Croweater; 05-08-2021 at 12:43 AM.
Originally Posted by Croweater
Code in Sheet2;
In column D it will put the date and time when it was changed.
Please Login or Register to view this content.
i have another problem with same question
the follow attachment have one sheet only, column (B:M).cells is formula link to other excel
when cells.value changes. the old value paste on (O:Z)
i trying use #3 code but is does not work on it
Last edited by ray510; 05-08-2021 at 01:49 AM.
Try;
Please Login or Register to view this content.
it is not workTry;
Please Login or Register to view this content.
OK, when someone says to me 'It doesn't work', without adding any value whatsoever, such as;
'I got (this error) at (this line)'
or
'It didn't compile, it came up with (this error)'
or
'I copied the code into Sheet1, and it added the old values to the wrong columns'
Or something else like that, I lose interest VERY quickly. As you are new, I'll maintain my interest for now.
So why didn't it work?
i am sorry
Attachment 731619
left excel in the attachment
i have copied the code into sheet1 and cells b2 is link to the right excel cells b2
right excel in the attachment
i have try to change the number of cells b2, left excel cells b2 number has change but the old value cannot show on cells o2
should i add any code for link to other excel?
Last edited by ray510; 05-08-2021 at 04:24 AM.
Maybe try making a copy of the cells (helper range) after a calculation? (exacty the same idea as already suggested in post#2 for your earlier example)
Please Login or Register to view this content.
It's work. And I try this calculating when workbook is open, it take a long time to run. Maybe have much row should Calculate in one sheet.Maybe try making a copy of the cells (helper range) after a calculation? (exacty the same idea as already suggested in post#2 for your earlier example)
Please Login or Register to view this content.
Thank you for help me to solve the problem
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks