Macro needs to repeat itself until cell value = 0

[jtsains]

I am not sure if anyone can help me on this because it involves data in a program outside of Excel; however, what i am looking for is to make the macro I have loop and repeat itself based on the values in Excel.

If my data begining in A1 includes a number that I am looking up in my program and the macro as i have it written works perfect. The problem is that i need the macro to look at cell A2 and rerun the macro then go to A3 and so on until there is no data in Column A to be searched.


Thanks in advance for your support.

Sub WPGM()
Range("A1").Select
Selection.Copy
Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey
Pause

Session.TransmitANSI "3"
Paste
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
CurPos 19, 22
Pause
Session.TransmitANSI "R2011/5 "
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
Session.TransmitTerminalKey rcIBMPf2Key
Pause
Session.TransmitTerminalKey rcIBMPf3Key
Next

End Sub

 

[kpark91]

You can call any subs by using

Call Macro1

So,,,

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Call Macro1
Next i

 

[jtsains]

Would you be able to explain the code?

What does this mean?

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

If i understand what the code is doing exactly it helps me understand how to write specific code in the future too.


This code appears to be counting the number of values in column A and then going to the end and selecting up one, but how does that help with the looping?

 

[kpark91]

Hi, jtsains.

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
is a type of for loop

where it will execute the code inbetween the For and Next for a number of times.
and i is a variable that will keep track of how many loops the code has run.

so, when we put i = 1 To 100, it means
the for loop will run from 1 to 100.

In our code, it says i = 1 To Range("A" & Rows.Count).End(xlUp).Row, it means
the for loop will run from 1 To the row number of the last row in column A.

Now to explain Range("A" & Rows.Count).End(xlUp).Row,
Rows.Count count the number of used rows but what actually happens internally is that it returns the number of a first found empty cell in column A.
For example, if you had 100 rows of data in column A but there was an empty cell at the 50th row of your data in column A, Rows.Count would return 50.

Range("A" & Rows.Count) will basically reference the last empty cell.
.End(xlUp) will move up from the referenced cell
.Row will return the number of the row of the referenced cell.

So, in the end, Range("A" & Rows.Count).End(xlUp).Row will return the number of the last row found in column A, allowing you to loop through all of your cells in column A.


For more explanation of the loops:
http://www.classanytime.com/mis333k/sjloopsarrays.html

 

[jtsains]

Excellent detail!

Thank you so much!

Since my code only tells it to look at Cell A1; what portion of the code you had me add will tell it to copy cell A2 on down until it runs out? The code I wrote is only looking at A1 and copying that number.

The loop you suggested can work, but i needs to not just rerun the same copied from cell.

Thanks for your help.

 

[jtsains]

Did I add the code correctly, so that it will copy the number from each cell in Column A?

Sub WPGM()

Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey
Pause

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

Selection.Copy
Session.TransmitANSI "3"
Paste
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
CurPos 19, 22
Pause
Session.TransmitANSI "R2011/5 " ' need to have this information copied from excel also if possible.
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
Session.TransmitTerminalKey rcIBMPf2Key
Pause
Session.TransmitTerminalKey rcIBMPf3Key

Next i

End Sub

 

[jtsains]

I ran this code on a test section and it copies only the values in Cell A1 and then pastes it and loops around and recopies values in Cell A1 and pastes it again.

I have 7 different numbers in column A and so it loops around 7 times, but never changes which number it is copying and pasting.

How do I get it to select the next cell value to copy?

 

[Desu Nota from Columbus]

Sub WPGM()

Dim i as Integer
Call ConnectWRQ
        Session.TransmitANSI "WPGM"
        Session.TransmitTerminalKey rcIBMEnterKey
        Pause
        
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
   Range("A" & i).Select
          
Selection.Copy
        Session.TransmitANSI "3"
        Paste
        Pause
        Session.TransmitTerminalKey rcIBMEnterKey
        Pause
        CurPos 19, 22
        Pause
        Session.TransmitANSI "R2011/5    "  ' need to have this information copied from excel also if possible.
        Pause
        Session.TransmitTerminalKey rcIBMEnterKey
        Pause
        Session.TransmitTerminalKey rcIBMPf2Key
        Pause
        Session.TransmitTerminalKey rcIBMPf3Key
     
Next i
               
       End Sub

your code refers to selection, but you didn't specify what to select. I've supplied a possibility, check to see if it works.

 

[jtsains]

This works wonderful for what I am currently using it for.!

I have a couple of follow up additions.
If Cell A1 will be a header in my sheet now instead of the begining of data; what code changes to start my data on A2, instead of A1?


Also, If I want G2 to be copied and pasted instead of using this line in the code:
Session.TransmitANSI "R2011/5 "

What can I add to copy the related cell in row G and then paste again?

 

[Desu Nota from Columbus]

This works wonderful for what I am currently using it for.!

I have a couple of follow up additions.
If Cell A1 will be a header in my sheet now instead of the begining of data; what code changes to start my data on A2, instead of A1?

Change

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row

I'm am not entirely sure for your second question--I'm not familiar with that line of code and what it means. Hopefully kpark91 looks back (he/she knows his/her stuff)