Using a loop and time delay

[Tenspeed39355]

Hi guys Well I am back again with another project.
I am trying to set up an Excel program. The following is what I would like to accomplish with your help. Using Excel I have Column A3:A650 with stock symbols.
I would like to take each symbol and one at a time copy and paste each symbol to $D$1, wait five (5) seconds and the move the next symbol in Column A to $D$1 and so on until
the program reaches the last one in column A at which the program will stop. I am sending you a short list of the symbols for you to test. The last time I had a problem you guys hit the nail on the head and helped me out of a bind. Again thanks for any help with this.




ACG


ADX


AFB


AGC


AGD


AKP


AOD


APB


APF


APX


ARK


ASA


ASG


ASP


AVK


AWF


AWP


AYN


BAF


BBF


BBK

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

.Cells(i, "A").Copy .Cells(i, "D")
Application.Wait Now() + TimeSerial(0, 0, 5)
Next i
End With
End Sub

[Tenspeed39355]

Thanks for your super quick reply. I ran the macro and found there needs to
be one change. When the symbols move to D the next symbol is entered in D3
the next symbol is entered in D4 and so on. I need for ALL THE SYMBOLS to remain
in $D$1. Can you make that change? It should go like this. The first symbol in entered
in $D$1, it remains there for the three seconds. The next symbol is then entered in
$D$1 and it remains there for three seconds and so on. Can you make that change?
Thanks
Max

[Sean.DiSanti]

another way to skin the cat

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 < 5
DoEvents
Wend
x = x + 1
Wend
End Sub


***edit*** and you changed your time constraint in your second post, if you want 3 seconds instead of 5, just change the 5 to a 3

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

.Cells(i, "A").Copy .Range("D1")
Application.Wait Now() + TimeSerial(0, 0, 5)
Next i
End With
End Sub

[Tenspeed39355]

Hi Mr. Lord of VBAX. lol. I want to thank you again for your time with this. The last post
was just right on the money. With your data I can now sit back and sip a cup of coffee and watch the data flow. I used VLookup to search in a number of my ss to bring back
data showing which Closed End funds make my screens. I have around 15 peices of data that show if a fund is looking like something I would invest in. Before using your Loop program I would have to enter the symbol for each fund I wanted to look at.
I think that the time needs to be a little longer so I can see all the data so I am going to make the five (5) seconds to a larger number, around seven. I can do that with no problems. Again THANKS. I will rate this post ten (10) stars.
Max