Jump out of loop when satisfied a condition

[poem2004]

Good morning,

I have a macro that uses data from an excel column to plug into attachmate and then do further check from that data. On the attachmate screen, that customer might have more than one sub accounts. The code would go into each sub account and check for a status if it a yes, it would place a "Y" back to excel on the next column of the main account. The attachmate screen can only display 7 rows of sub accounts, each sub account might have a status Y or N. I would like to have the code check these sub account until it finds a Y status and then jump out of the loop - no further check - and then it would go back to pull the next main account on the next row of excel.

My problem is that I don't know how to jump out of the loop to stop checking on the next sub account when it finds a Y on the previous sub account (wasting time here). Should the 3rd sub account has a Y status, the code will not need to check on the 4th sub account or 5th or 6th, but it would go back to start on the next main account from excel.

On the attachmate screen, there could be more than 7 sub accounts if Pf8 is pressed to the next page. How can I have the code to check on the next 7 sub accounts until it finds a Y. A DO or a FOR loop could be a big help but I don't know how to apply them.

I have no background programming. Any help would be appreciated and thank you in advance.

Below is my code that goes through 7 sub accounts to check:
----------------------------------------------------
' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 200 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' ------------------This section of code contains the recorded events





'Declare the Excel Object
Dim xlApp As Object, xlWorkbook As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True


xlApp.Workbooks.Open FileName:="M:\Pittsburgh\CF\CLS_DCE\DSC-PA\Pull List\Get-It Pull List test.xls"


Set xlSheet = xlApp.worksheets("Sheet3")
Set MyRange = xlApp.activesheet.Range("A1:A402")


Dim Row As Long


With xlApp.worksheets("Sheet3")
Set MyRange = .Range("A1:A402").Resize(xlApp.CountA(.Range("A1:A402")))
End With


For Row = 1 To MyRange.Rows.Count 'first row of range which is cell A1


Sess0.Screen.PutString MyRange.Rows(Row).Value, 11, 41 '-----------for General customer screen, use this line

'Sess0.Screen.PutString MyRange.Rows(Row).Value, 05, 30 '---------for Dynamic Teleporting sreen use these 3 lines
'Sess0.Screen.PutString "s", 5, 53
'Sess0.Screen.PutString "s", 5, 61


Sess0.Screen.SendKeys("<ENTER>")
Sess0.Screen.Sendkeys("<Pf15>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString "s",18,21
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Pf16>")



'---------------Start input and check for BIC here. A Fed Pledged "Y" will be returned
'--------------row 1
If Sess0.Screen.GetString(7,7,1) <> "" Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))

If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"
End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 2
If Sess0.Screen.Area(9,7,9,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"
End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 3
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

If Sess0.Screen.Area(11,7,11,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab><Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"
End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 4
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
If Sess0.Screen.Area(13,7,13,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab><Tab><Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"
End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 5
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
If Sess0.Screen.Area(15,7,15,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab><Tab><Tab><Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"
End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 6

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
If Sess0.Screen.Area(17,7,17,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab><Tab><Tab><Tab><Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"

End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
'--------------row 7
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
If Sess0.Screen.Area(19,7,19,7).Value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Tab><Tab><Tab><Tab><Tab><Tab>s<ENTER><ENTER>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
FedPledge = Trim(Sess0.Screen.GetString (18,18,1))
If FedPledge = "Y" Then
xlSheet.Cells(row,2).value = "BIC"

End If
Sess0.Screen.Sendkeys("<Pf12><Pf12>")

End If
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset>") ' press Esc to reset screen when it detects no obligation


'---------------------------end checking BIC for 7 rows of page 1

Sess0.Screen.Sendkeys("<Pf12><Pf12><Pf12>") '--------this line for general customer screen
'Sess0.Screen.Sendkeys("<Pf12>") '---------add this line for Dynamic Teleporting screen
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Next Row

' This section of code contains the recorded events

xlApp.Save
xlApp.Workbooks.Close
xlApp.Quit

System.TimeoutValue = OldSystemTimeout

MsgBox "Done Checking for BIC. Please run check SWAP."

End Sub

 

[SkipVought]

hi,

Have not perused your code, but to answer your question...

do while([i]Some Expression[/i])
  if [i]Some OTHER Expression[/i] then Exit do
loop

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[poem2004]

Skip,

Thanks for a quick reply.
OK, after plug in the main acct, I might have up to 10 or more sub accts, but the screen can only display up to 7 sub accts. It looks like this:

- sub account 1
- sub account 2
...
- sub account 7

The code would select starting from sub account 1 at location 7,7,1 press enter twice and then goes to location 18, 18, 1 to check if the status of FedPledge. If status is a N, it would go to the sub acct 2. If status is a Y, it would place a "BIC" on excel, exit the loop and then start checking on the 2nd main acct. The code that I posted was able to do this task, but it keeps checking all the 7 sub accts even if it found a Y from the previous sub acct.

I have 7 If statements to go through for each 7 sub accts. Should it look like this:

For i = 1 to 7
'--------row 1 (sub acct 1)
Do while status = N

If status = Y Then
update to excel
Exit Do
End If


'--------row 2
...

'---------row 3

Loop

Next i

Please guide me through the possible steps to combine these loops together.

 

[SkipVought]

This puzzles me, as the if statement will NEVER be executed

        Do While Status = "N"  '[b]wherever Status becomes "Y" this loop ends![/b]
            If Status = "Y" Then

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]