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

 

[jtsains]

Thank you for your help.

the Code: Session.TransmitANSI "R2011/5 "
Just tells Excell to manually type in the phrase in text: "R2001/5 "

I know how to copy values from excel, and then past them where i want them; my main concern is ensuring that the macro copies the correct value from column G.

So the macro starts looking at Cell A2 and copies those values then pastes it into my program which is basically a database. it fines the records equal to those values and then i want it to paste the values in Cell G2 into a specific field.

Again, i know how to make the macro work with my database; but what I can't seem to do is get it to copy the correct data from excel based on which feild it is looking at in Column A.

 

[Desu Nota from Columbus]

Thank you for your help.

the Code: Session.TransmitANSI "R2011/5 "
Just tells Excell to manually type in the phrase in text: "R2001/5 "

I know how to copy values from excel, and then past them where i want them; my main concern is ensuring that the macro copies the correct value from column G.

So the macro starts looking at Cell A2 and copies those values then pastes it into my program which is basically a database. it fines the records equal to those values and then i want it to paste the values in Cell G2 into a specific field.

Again, i know how to make the macro work with my database; but what I can't seem to do is get it to copy the correct data from excel based on which feild it is looking at in Column A.

Is column G populated with data the same as column A?

I'll repeat that I am not familiar with the program you are using in conjunction with excel, but you should be able to adjust the code to repeat the same procedure for column G (changing where the data is pasted of course).

 

[jtsains]

Yes column G is populated with Data relavant only to the record information in column A.

So if for example i find record number 12345 from column A and i want to update my database with text value R2011; column G will have the text R2011 to be copied and pasted.

When i move to A3 and record number 12346 is found; the text value may change specific to that record number and column G may be text values of : R2009 etc. The values in column G will be manually populated based on what needs to be updated with each record before the macro is ran.

 

[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
     
For i = 1 To Range("G" & Rows.Count).End(xlUp).Row
   Range("G" & i).Select
        Selection.Copy
        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 i
        
       End Sub

Could you use the above and tailor the part in red to dump the data from column G where you want it?

And ALWAYS test macros on sample data!!!!

And this is not the most concise way of performing this feat.

 

[jtsains]

Here is what it would have to look like; and i am concern that this doesn't ensure the value in Cell A2 relates to the value in Cell G2:

(PS, I am fairly new to the forum so if you can tell me how to post my code to a seperate box like you are doing, It might be easier for readers.)
***
Sub WPGM()

Dim i As Integer
Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey
Pause

For i = 2 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

For i = 2 To Range("G" & Rows.Count).End(xlUp).Row
Range("G" & i).Select
Selection.Copy
Paste
Next i

Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
Session.TransmitTerminalKey rcIBMPf2Key
Pause
Session.TransmitTerminalKey rcIBMPf3Key

Next i

End Sub


Also, how do I add an If statement that provides an exit of loop if value in Cell A returns an "invalid Number" response from my database? Again, I can program excel to see where it looks for the "invalid number" in my database but i need to know that if this response is given, it will stop the loop and provide a msgbox stating: "number invalid"

 

[Desu Nota from Columbus]

They are called code tags: (Without the spaces)

[ c o d e ]
Your code here
[ / c o d e ]

 

[Desu Nota from Columbus]

I think we have surpassed my knowledge of VBA now.

I know you can use the code:

On Error _______(do something)_____

I wish I could help, but I have much to learn still.