PDA

View Full Version : Moving contents of cell down one cell



Tenspeed39355
02-28-2011, 05:50 PM
Hi I have tried all I know so here is the problem I would like to slove.
Cell J27 is the result of a loop I have going. I also have a timer going also.
Cell J27 has a new stock symbol appearing every five seconds. I would like
the old stock symbol to move down one cell to J28. When the next stock
symbol appears in J27 the old one will move to J29 etc until the loop is finished. Can I use Excel formatting or will I need to use a macro?
Thanks for any help with this.
Max

draco664
02-28-2011, 06:04 PM
Hi I have tried all I know so here is the problem I would like to slove.
Cell J27 is the result of a loop I have going. I also have a timer going also.
Cell J27 has a new stock symbol appearing every five seconds. I would like
the old stock symbol to move down one cell to J28. When the next stock
symbol appears in J27 the old one will move to J29 etc until the loop is finished. Can I use Excel formatting or will I need to use a macro?
Thanks for any help with this.
Max

What sort of loop are you using?

If you are using a VB loop of some sort to get the refreshed data, you could put the following code in the loop before the next data refresh.

Range("J28").Select
Selection.Insert Shift:=xlDown
ActiveCell.Value = Range("J27").Value

That will push the current values from J28 down, down one cell and put the value of J27 into J28 before J27 gets updated. Just be carefull though, a long enough loop would push the data off the end of the sheet... :whistle:

Chris

Tenspeed39355
03-01-2011, 08:51 AM
Chris This is what I am using. I how it makes sense to you.

Public Sub ProcessData()
x = 1
While ActiveSheet.Range("a" & x).Formula <> ""
ActiveSheet.Range("d1").Formula = ActiveSheet.Range("a" & x).Formula
ltime = Timer()
While Timer() - ltime < 2
DoEvents
Wend
x = x + 1
Wend
End Sub


The following puts the symbol from D1 in J27



=IF((AND(C27="pass",D27="pass",E27="pass",F27="pass",G27="pass",H27="pass",I27="pass")),D1,"")

draco664
03-02-2011, 04:47 AM
Try this.

Public Sub ProcessData()
x = 1
While ActiveSheet.Range("a" & x).Formula <> ""
Range("j28").Insert xlDown
Range("j28").Value = Range("j27").Value
ActiveSheet.Range("d1").Formula = ActiveSheet.Range("a" & x).Formula
ltime = Timer()
While Timer() - ltime < 2
DoEvents
Wend
x = x + 1
Wend
End Sub

Good luck,

Chris

Tenspeed39355
03-02-2011, 05:12 AM
Chris Thanks That works great. Now all I have to do is start the macro
and sit back and watch it go to work. Before I had to copy cell J27 to a
hard copy so I had to set the timer to 5 seconds which took some time to
run thru 600 symbols. I have the timer set to one (1) second. Again thanks
I will rate this thread