×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Jump out of loop when satisfied a condition

Jump out of loop when satisfied a condition

Jump out of loop when satisfied a condition

(OP)
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

RE: Jump out of loop when satisfied a condition

hi,

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

CODE

do while(Some Expression)
  if Some OTHER Expression then Exit do
loop 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Jump out of loop when satisfied a condition

(OP)
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. bigsmile

RE: Jump out of loop when satisfied a condition

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

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close