Update account history

[Rocky2013]

Hi,
I need a VBA or function if it is possibe to update the account history.
Once there is an new update, the current date and value will change
from a formula to a value, then "=" formula will be added to the next
row.

Currently I am doing this manually to copy & pate value first before
I change the new update. There are multiple worksheets doing the
same. But sometimes I forgot to first copy and paste. Hopefuly VBA
will automate that.

I attched an example hope that will make it easy to explain.

Thanks

[skywriter]

Not clear on what you mean.

If D4 changes to 02/15/15, then in account history, L10 and M10 will copy and paste VALUE to the same cells.

Understand the if D4 changes part, but there's nothing in L10 and M10, so what does this mean. Are we copying to L10 and M10, if so what?

Do you really want the formulas or just the values?

Next row below, add formulas "= D4" to cell L11, "=F12" to cell M11 .

Is what is now F12 going to be expanding down the sheet?

[Rocky2013]

Not clear on what you mean.


Understand the if D4 changes part, but there's nothing in L10 and M10, so what does this mean. Are we copying to L10 and M10, if so what?

Do you really want the formulas or just the values?
I review my file, I found something wrong what I wrote. I wrote the formula and steps of instruction.
Orginally I try to use VBA to add = value in columns L and M. Then later I use if functions to preformate
the cells in the next row. L10:O10 suppose to show the values. Because I did the formula wrong, the cells
do not show any values.

So before changing to a new day, 02/15/15 in D4; L10 and M10 should show as values instead of formula.
If I do not change, L10 and M10 will be updated with the new date and values. I want to keep building the
account history for every update.

Is what is now F12 going to be expanding down the sheet?

No; F12 will not keep expanding down. F12 will keep updating
along with the account history update.


I have to be carefull on my example. I am confuse too when I read it.

I could try to use marco recording, I try not to click a button to update
rather a real time update.
Thanks of your interest to help.

I am sorry for the confusion, I worte the instructions when there is no if funtions in columns L:O.

[JOHN H. DAVIS]

See if this does what you want?

[Rocky2013]

Hi John,
Thanks for the help. It does not do I want to do because
I screw on the first Excel file. If I enter a new day, the last
account history should change from a formula to a value.

The second excel file has the update. Can you please use that
to recode. I do not know how to figure you code to work with the
file.

Thanks

[Rocky2013]

REPEAT, SORRY!

Hi John,
Thanks for the help. It does not do I want to do because
I screw on the first Excel file. If I enter a new day, the last
account history should change from a formula to a value.

The second excel file has the update. Can you please use that
to recode. I do not know how to figure you code to work with the
file.

Thanks

[JOHN H. DAVIS]

OK. Then try this one?

[Rocky2013]

Hi,
This is what I find.
On the account history, M10 is greate than M9, profit should be a "Gain" in the last transaction.

I notice that update stock data needs to update first, then enter the new date to add new row.
My orginal thought is enter a new day, but that is OK.

There is dash border line around K9:O9, can that disappears after running the code?
I hit an escape key manually to close the dash border.

Thanks

[JOHN H. DAVIS]

Try it now. Let me know what happens.

[Rocky2013]

Hi,
When I change the share number, F12 does not change.
So I add a formula SUM(F10:F11) to F12.

It is working now.

Thanks

[JOHN H. DAVIS]

Glad to here you got a working solution.