IF x THEN Paste Value?

[laleph]

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]

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

Is Column C also a VLOOKUP? I tested by putting values in Column C above the current date (replacing those FALSEs). Those values did not change when I ran the macro?

 

[laleph]

Column C is not a VLOOKUP, I think it's the result of having run other Macro's earlier which simply pasted Column B to Column C, (which would only return the current day's data). When I run your Macro litterally nothing happens to my SS. If you wanted to e-mail me your example my e-mail is laleph@aol.com.

Thanks again SO much for trying to help me out!

-Lars

 

[John Davis]

Column C is not a VLOOKUP, I think it's the result of having run other Macro's earlier which simply pasted Column B to Column C, (which would only return the current day's data). When I run your Macro litterally nothing happens to my SS. If you wanted to e-mail me your example my e-mail is laleph@aol.com.

Thanks again SO much for trying to help me out!

-Lars

I just emailed it. Are you running another macro after this one? Those FALSEs in Column C are very suspect. Do you have a copy of the other macro? Perhaps there's a simpler solution there. Anyway, I think we're getting closer too a solution.

 

[laleph]

I think I was using the initial code you gave me from your initial response, however removed that code when I tried this most recent itteration.

Even the one you sent me does nothing when I run the Macro. Is it possible I'm somehow running it incorrectly?

Could I be causing problems by manually changing B3 on sheet 2 to try and trick excel into thinking it's tomorrow and then trying to run the Macro? I want to run a macro, bring JUST today's (or the day listed on Sheet2 B3) portfolio value to column C, and not any other days. Are we overthinking this one? Is there a simpler way of going about it?

I really do feel like we're getting closer and again, can't even begin to express my gratitude for the time your spending to help me out!

 

[John Davis]

I think I was using the initial code you gave me from your initial response, however removed that code when I tried this most recent itteration.

Even the one you sent me does nothing when I run the Macro. Is it possible I'm somehow running it incorrectly?

Could I be causing problems by manually changing B3 on sheet 2 to try and trick excel into thinking it's tomorrow and then trying to run the Macro? I want to run a macro, bring JUST today's (or the day listed on Sheet2 B3) portfolio value to column C, and not any other days. Are we overthinking this one? Is there a simpler way of going about it?

I really do feel like we're getting closer and again, can't even begin to express my gratitude for the time your spending to help me out!

OK. After reviewing the posts on this thread. I think I know what you want. But it's probably best for me too start all over. It'll take sometime as I'm in the office and busy jumping from one thing to another. It shouldn't take long though.

 

[John Davis]

I think I was using the initial code you gave me from your initial response, however removed that code when I tried this most recent itteration.

Even the one you sent me does nothing when I run the Macro. Is it possible I'm somehow running it incorrectly?

Could I be causing problems by manually changing B3 on sheet 2 to try and trick excel into thinking it's tomorrow and then trying to run the Macro? I want to run a macro, bring JUST today's (or the day listed on Sheet2 B3) portfolio value to column C, and not any other days. Are we overthinking this one? Is there a simpler way of going about it?

I really do feel like we're getting closer and again, can't even begin to express my gratitude for the time your spending to help me out!

OK. Here's another go at it.

Sub laleph()
Dim lr As Long
Dim cCell As Range
Dim x As Range


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

Set x = Range(Range("B3"), Range("B" & lr))

With x

    .Formula = "=IF(A3=Sheet2!$B$3,Sheet1!$L$11,"""")"
    .Value = .Value
    
End With

For Each cCell In x

    If cCell.Value <> "" Then cCell.Offset(0, 1).Value = cCell.Value
        
Next

End Sub

 

[laleph]

IT WORKS IT WORKS IT WORKS!!! How did you do that! I can't even begin to thank you enough! If there's anyway I can repay you let me know! You sir are THE MAN! This makes my day and then some! THANK YOU!!!

 

[John Davis]

IT WORKS IT WORKS IT WORKS!!! How did you do that! I can't even begin to thank you enough! If there's anyway I can repay you let me know! You sir are THE MAN! This makes my day and then some! THANK YOU!!!

You're Welcome. I only changed the type of loop. Don't know why one works and the other didn't. But glad it helped.