# IF x THEN Paste Value?

#### laleph

##### New Member
I'm currently trying to create an investment portfolio tracker, however I'm having some trouble tracking my data on a day to day basis.

Right now I have a section for my portfolio which imports external data for share prices, which then calculates change in value, and my overall portfolio value. Getting my portfolio value on any given day is rather easy, however I'm running into trouble trying to automatically track it's value.

Currently I have 3 columns, Date (A), Value (B), and Pasted Value (C). Date lists every day (May 2, May 3, May 4... etc). Value uses the VLOOKUP function and says "IF external data date = Date in column A, then return portfolio value."

Up to this point I'm good. I run into problems when all of a sudden we get to the next day (Say... May 3rd), and suddenly we lose the portfolio value from today (May 2nd) because the date of the external data no longer = May 2nd.

What I want to do is paste the value of Column B into column C, so any time in the future, an old value of the portfolio on any given day is accessible. I realize I could do this manually, however I'd like this step to be completed automatically.

Thank you SO much for you help, and feel free to ask me if you have any questions.

#### John Davis

##### Well-known Member
I'm currently trying to create an investment portfolio tracker, however I'm having some trouble tracking my data on a day to day basis.

Right now I have a section for my portfolio which imports external data for share prices, which then calculates change in value, and my overall portfolio value. Getting my portfolio value on any given day is rather easy, however I'm running into trouble trying to automatically track it's value.

Currently I have 3 columns, Date (A), Value (B), and Pasted Value (C). Date lists every day (May 2, May 3, May 4... etc). Value uses the VLOOKUP function and says "IF external data date = Date in column A, then return portfolio value."

Up to this point I'm good. I run into problems when all of a sudden we get to the next day (Say... May 3rd), and suddenly we lose the portfolio value from today (May 2nd) because the date of the external data no longer = May 2nd.

What I want to do is paste the value of Column B into column C, so any time in the future, an old value of the portfolio on any given day is accessible. I realize I could do this manually, however I'd like this step to be completed automatically.

Thank you SO much for you help, and feel free to ask me if you have any questions.
Does this help any?

Code:
``````Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

Range("C" & i).Value = Range("B" & i).Value

Next i

End Sub``````

#### laleph

##### New Member
It does a great job of pasting values to column C, however when I run the macro again (on another day for example). I want the old value to remain. So if my portfolio was worth \$112,500 yesterday, and today it calculates my portfolio to be worth \$113,000, I want to see both yesterday's value, and today's value in column C. That is... I don't wany the values of any past dates to dissappear. Currently when I run the code, today's value will update perfectly, but yesterday's will dissappear.

#### Mefu

##### New Member
So ... you want to keep the values in column C if any ?
If yes you will have to modify John's line from the loop with

Or ... if you want to keep the values from yesterday maybe you will have to insert another column for today. In this way you will be able to do the math and compare with the previous day.

#### laleph

##### New Member
I want to keep all past values and update new values as they change. I modified John's loop as you described, however it still reacted the same way.

Maybe the best way of describing my situation is I have a column of dates, and a column of end-of-day stock prices. When I get my external data it only gets today's stock value (May 2nd), but when I update by external data tomorrow, there will be nothing for May 2nd, only May 3rd, hence forever losing May 2nd value. Once I have a value for May 2nd I want to paste its value into a column next to it, even when tomorrow, when it tries to pull external data for May 2nd, none will be there.

Does this make sense? Should I attach a file? Thanks again for your help!

-Lars

#### John Davis

##### Well-known Member
I want to keep all past values and update new values as they change. I modified John's loop as you described, however it still reacted the same way.

Maybe the best way of describing my situation is I have a column of dates, and a column of end-of-day stock prices. When I get my external data it only gets today's stock value (May 2nd), but when I update by external data tomorrow, there will be nothing for May 2nd, only May 3rd, hence forever losing May 2nd value. Once I have a value for May 2nd I want to paste its value into a column next to it, even when tomorrow, when it tries to pull external data for May 2nd, none will be there.

Does this make sense? Should I attach a file? Thanks again for your help!

-Lars
Not sure I understand, but perhaps:

Code:
``````Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

If Range("A" & i) > Range("A" & i).Offset(-1, 0) Then Range("C" & i).Value = Range("B" & i).Value

Next i

End Sub``````

#### laleph

##### New Member
That still doesn't keep old data stored, it simply deletes and updates today's. I have attached an example spreadsheet. The left is what I have and am working with. The right is what I'd like the final product to look like. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you SO much for your help!
<o></o>
http://www.4shared.com/file/uofupWe7/Day_By_Day_Investment_Tracker_.html

-Lars

#### Mefu

##### New Member
Maybe you will want to import the data in the column D and keep at the beginning columns B and C for previous days.
Then brush the data and replace B with C and only if C<> D replace cell in C. Then delete column D.
In this way you you will have B and C to compare two days.

#### John Davis

##### Well-known Member
That still doesn't keep old data stored, it simply deletes and updates today's. I have attached an example spreadsheet. The left is what I have and am working with. The right is what I'd like the final product to look like. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you SO much for your help!
<o></o>
http://www.4shared.com/file/uofupWe7/Day_By_Day_Investment_Tracker_.html

-Lars
I tested this one on the file you sent:

Code:
``````Sub laleph()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step -1

If Range("A" & i) >= Date Then

Range("C" & i).Value = Range("B" & i).Value

Range("C" & i).Value = Range("C" & i).Value

End If

Next i

End Sub``````
I think it works, unless I'm missing something. I assumed that the value in Column C is what you wanted too keep since Column B will change daily. One question - Why are there "FALSE"s in Column C from the current date down. Is there something else going on (ie a formula or something)?

#### laleph

##### New Member
Exactly! Column B will change daily, and I want to keep the value in column C. There are "FALSE"s in Column B because I had column B previously setup with a VLOOKUP saying, "IF Column A (Date) = Date of imported Data (Sheet 2 B3), THEN return value of portfolio, otherwise return "FALSE""

Unfortunately that code didn't seem to work for me. When I ran the Macro litterally nothing happened.

Is there perhaps a way to write a macro that detects changes on sheet2. If sheet2 changes. The macro will update the appropriate line automatically on sheet1. This way the value would be a real value and not a formula that would change when the data changes on sheet2?

Again thank you SO much for your help. If I can somehow repay you once this bad-boy ends up working PLEASE let me know!

-Lars

1,082,336
Messages
5,364,706
Members
400,811
Latest member
MSBINinja

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...