Copy paste values from daily web query

[Simensg]

Hi. I have a web query which is updating stock prices daily (sheet 2). I have an IF formula in sheet 1 which retrieves the daily quote if there is a match on the current date. The problem is that I have to copy/paste values each night to prevent the formula to mess up yesterdays quotes. Is there a formula / code to prevent this from happening? Dreamworld: "If match then paste values". Thanks in advance

 

[David Evans]

Post your Excel file ...

 

[Simensg]

Thanks. Here is the file.

 

[Nebu]

Hi:

May be this macro

Private Sub Worksheet_Calculate()
i& = Sheets("Avkastning DnB GI").Cells(Rows.Count, 4).End(xlUp).Row + 1
If Sheets("Avkastning DnB GI").Cells(i, 3) = Sheets("Query1").Cells(23, 2) Then Sheets("Avkastning DnB GI").Cells(i, 4) = Sheets("Query1").Cells(7, 4) Else: Sheets("Avkastning DnB GI").Cells(i, 4) = ""
End Sub

Thanks

 

[Simensg]

Great! Thank you

 

[Simensg]

Hi again. Is it possible to alter the code so that values are copied if dates are matching regardless of the first blank cell? For instance if the date is not the first blank cell? Thanks.

 

[Nebu]

Hi:

Try the following Code.

Private Sub Worksheet_Calculate()
i& = Sheets("Avkastning DnB GI").Cells(Rows.Count, 4).End(xlUp).Row + 1
If Sheets("Avkastning DnB GI").Cells(i, 3) = Sheets("Query1").Cells(23, 2) Then Sheets("Avkastning DnB GI").Cells(i, 4) = Sheets("Query1").Cells(7, 4) Else: Sheets("Avkastning DnB GI").Cells(i, 4) = "N/A"
End Sub

Thanks

 

[Simensg]

Nice Thank you very much! Is it possible to autmomate this even further? Let say the date entered is 10 days after last non-blank cell. Is there an alternative to pound the radio button 10 times ?

 

[Nebu]

Hi:

I did not understand what you meant here, where is the radio buttons in your file?

Thanks