Horns of a dilemma!
Posted by Richard Larocque on January 31, 2002 5:50 AM
OK! Here's the problem. I have three columns. Column A is labelled Dates, B is labelled Price. What I'm looking to do is as follows: Every day, in Column C1, there will be a new value. I want the price in C1 to go also in column B. So for example, if on Jan.1,(Column A2) the price is 40, I would like the number 40 to appear in Column B2. On Jan.2, (Column A3)the price is 39, the same number appears in B3, and so forth. However, there is a catch! The numbers in the preceding days must remain the same! In B2, the 40 would not change whenever a new number appeared in C1. Can you help?
Posted by Andy Gee on January 31, 2002 6:11 AM
Why would the number change if in C2 you put =C2 etc. I feel I must have missed something in your question!
Posted by Rich on January 31, 2002 6:19 AM
He wants to use C1 as an input cell that feeds into the bottom of a list held in column B. Whatever he puts in cell C1 goes to the bottom of column B.
Richard, off the top of my head I'd say you need to do a macro that copies the value you put into C1 and pastes it at the bottom of column B. You can't do this with formulas to my knowledge.
Posted by Richard Larocque on January 31, 2002 9:23 AM
How do I do this macro?
Posted by Juan Pablo G. on February 05, 2002 3:02 PM
Try this macro in the worksheet module (Right click on the sheet's tab, select view code, and paste it there).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then Target.Copy Range("B65536").End(xlUp).Offset(1)
End Sub
It'll run whenever you change C1, and paste the new price to the next available row in Column B.
Juan Pablo G.
