PDA

View Full Version : Excel using Loop



Tenspeed39355
10-07-2012, 12:38 PM
Hi guys I am trying to use a Loop program to copy from Sheet1 column
A2:A700 the symbols to Sheet2 $A$1. I want to Loop thru all of Sheet1
column A2:A700 put each symbol in Sheet1 $A$2 wait 5 seconds then put the next symbol in Sheet1 $A$1.
I am sending you the code I am trying to use. Please correct my errors.
Thanks
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 Sheets("Sheets2").Select.Cells(i, "$A$1")
Application.Wait Now() + TimeSerial(0, 0, 1)
Next i
End With
End Sub

Bob Phillips
10-07-2012, 03:16 PM
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 Sheets("Sheets2").cells(i, "A")
Application.Wait Now() + TimeSerial(0, 0, 5)
Next i
End With
End Sub

Tenspeed39355
10-08-2012, 06:30 AM
Good morning. I did a copy/past of your last macro. I am getting a error
message run-time error 9. Subscript out of range. When I run the macro
there is a yelow background on the following.
.Cells(i, "A").Copy Sheets("Sheets2").cells(i, "A")

What is the problem? Thanks again for your help
Max

Bob Phillips
10-08-2012, 03:22 PM
It should probably be Sheet2 not Sheets2. Check your workbook.

Tenspeed39355
10-09-2012, 05:41 AM
Good morning.Changing Sheets2 to Sheet2 fixed the problem. I would like
to make one change. As the macro stands now the symbols in Sheet1 column A are being copied to Sheet2 column A. Can I have all the symbols
copied to sheet2 $A$1 using the time to delay the next symbol? This would be great
if this can be done. Again thanks for your help.
Max

Tenspeed39355
10-09-2012, 05:47 AM
I do not know if my reply went thru. Changing Sheets2 to Sheet2 fixed the
problem. I would like one more change. When copy to sheet2 could the macro copy all the symbols from Sheet1 column A to just Sheet2 $A$1?
If so that would be great. Again thanks for your help.

Bob Phillips
10-09-2012, 09:55 AM
What symbols are you referring to?

Tenspeed39355
10-09-2012, 02:51 PM
Hi I have stock symbols in sheet1 A2:A700. The above program is copying
the symbols to sheet2 A2:A700. I want the symbols to be copied to only
sheet2 $A$1. There is a time value to slow the process down.
Also once the macro starts i cannot change the sheets from sheet1 to sheet2.
How do I change from one sheet to the other while the macro is running.
Thanks

Bob Phillips
10-09-2012, 04:30 PM
Okay, that should be straight-forward

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 Sheets("Sheets2").Range("A1")
Application.Wait Now() + TimeSerial(0, 0, 5)
Next i
End With
End Sub

Now I understand why the wait!

snb
10-10-2012, 01:26 AM
Sub snb()
[sheet2!A1] = Join([transpose(sheet1!A2:A700)])
End Sub

Tenspeed39355
10-10-2012, 12:33 PM
This is transposing sheet2 at the end of the macro to a row. I did not need this. All I want is to have sheet2 active while the macro is running.
Max

Sub snb() [sheet2!A1] = Join([transpose(sheet1!A2:A700)]) End Sub

Bob Phillips
10-10-2012, 02:55 PM
That is par for the course.

Did my change sort it for you?

Tenspeed39355
10-11-2012, 02:51 PM
First let me say thanks for all your help. I did get the macro to switch from
sheet1 to sheet2 when the macro started running. This is just what I need.
So thanks for the help. One thing that needs to be changed in the above code.
.Cells(i, "A").Copy Sheets("Sheets2").Range("A1")
The word ("Sheets2") should be ("Sheet2") before I changed it I was getting
an error message.
I will rate this thread *******:cloud9: