Copy outcome of a formula into a different cell

[SRA]

Hi,

I'm writing a macro that's supposed to copy the values from a range of sourcecells (wich is the outcome of a formula) into different destinationcells when the sourcecells changes it's values. So when a calculation is made, the values of the sourcecells are copied without constantly overwriting the previous destination cell.

For example:


Column B Column C Column D Column E
Outcome of formula Copy1 Copy2 Copy3
Outcome of formula Copy1 Copy2 etc..
Outcome of formula Copy1 Copy2

How can this be done?

Thanks!

[brynbaker]

Let's see. You need code that runs on worksheet change, it must check that the change that triggers it is the cell you're working ith, otherwise exit.
Then it finds the first free space to the right and copies the cell there.

Please Login or Register  to view this content.

Good in xl2003 through 2013

[SRA]

How do I use it? I copied the formula in my workbook, but am not sure how to implement it in my model...

[brynbaker]

I've assumed your value to monitor is B2; change the address in the second line to what you need.
Then change the value and it should record change as you indicated.
You must be aware of filling the row of course. The 255 in the fourth line is a limit: you set it to the number of the final row you neeed to allow (within xl limit of course).

[SRA]

Sorry, i'm fairly new to this... What do you mean with change the address in the second line?

[brynbaker]

Every cell has an address, e.g. 2nd row, 2nd column is B2 or $B$2 as normally reported to he workseet_change event handler. This is called whenever ANY cell value changes and passed the address involved. SO;
1. we check if the cell being monitored indeed the one passed to the event handler macro
2. your question said column B; I suppose you may mean all rows in column B are to be monitored. The the first line would be

Please Login or Register  to view this content.

and probably that is the change you need to make. you may wish to limt the rows monitored if not the whole column; you would test target.row for this
3. the macro changes another cell (col c, d, e etc., so the event handler will fire for every monitor entered
4. I disable the event handler before reording avalue to prevent this. And of course switch it back or the event handlers will never fire. You could instead leave those lines out and rely on the column test.

[brynbaker]

I'm off to bed now, good luck.

[SRA]

haha, thanks for your help!