×
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!

*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

Move to the next cell in the loop if blank data is pulled from Screen

Move to the next cell in the loop if blank data is pulled from Screen

Move to the next cell in the loop if blank data is pulled from Screen

(OP)
Hello,
   
   Was wondering if someone might be able to help with some EB code?   My macro currently  grabs 10 lines of data (For Y = 10 to 19) from 5 screens (For P = 1 to 5), PF8  forwards to the next page if there is more data.  This runs in a loop using data in column B  (X)  until it is either blank or reaches a count of 200.  My issue is: the data in cell B10  may have less data to pull than that of cell B5.  It may only have 1 page  of data to  
pull vs 5 (For P = 1 to 5) and may also only have 1 line of data.  What happens in these  cases is the code continues to pull the data. When there is no longer any actual data on  
the screen it pulls the "blank" data and populates blanks in the following rows of my  excel spreadsheet.  Since there are no additional pages to pull it continues to pull the  
original line of data.  So I end up with 1 row of data followed by 9 blank rows, then on  row 10  I get the original line of data.  What I think I need to do is put in some sort  
of "if Trim(A2) = "" then"  ........what I need it to do when it comes to a blank area on  the screen is move on to the next cell in column B on not "End Sub" This is the part I can't figure out.

Thanks
 

RE: Move to the next cell in the loop if blank data is pulled from Screen

(OP)

        Global g_HostSettleTime%
        
        Sub Main


    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")    
    Set Sessions = System.Sessions

    g_HostSettleTime = 0001        

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

    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If Not Sess0.Visible Then Sess0.Visible = TRUE
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
        Dim xl As Object
        Dim Workbook As Object
        Set xl = CreateObject("xl.Application")
        xl.Visible = True
        
        Set Workbook = xl.Workbooks.Open("C:\Documents and Settings\rbarnett\Desktop\Macro Models\MODEL.xlsm")
        
       
        For X = 3 to  200
        
        If X < 200 Then
        C = C + 1
        End IF

        With xl.Worksheets("METHOD")
        R = 4                            
        ID = .range("A" & 3)
        MN = .range("B" & X)          
        End with

        
        If MN = "" Then
        Msgbox "MACRO IS COMPLETE"
        Workbook.Save
        xl.Quit
        Exit Sub : End If
 

        Sess0.Screen.Sendkeys("<HOME>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
        Sess0.Screen.Sendkeys(ID)
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
        Sess0.Screen.MoveTo 07, 15
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
        Sess0.Screen.Sendkeys("x<Enter>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
        Sess0.Screen.Sendkeys("AOACMN")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
      
        Sess0.Screen.Sendkeys(MN)
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
        Sess0.Screen.Sendkeys("<Enter>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        
        Sess0.Screen.Sendkeys("x<Enter>")
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
        Sess0.Screen.Sendkeys("<PF11>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
        Sess0.Screen.MoveTo 09, 11
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
               
        Sess0.Screen.Sendkeys("x<Enter>")
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 
        For P = 1 to 5
        For Y = 10 to 19
        
        A2 = Sess0.Screen.GetString(Y, 05, 16)
        Workbook.Worksheets("METHOD X-REF").Cells(R, C).Value = A2
        R = R + 1
        
        Next Y
        
        Sess0.Screen.Sendkeys("<PF8>")
        Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Next P
   
        IF Y = 20 Then
        Y = 10
        End IF  
    

        Sess0.Screen.Sendkeys("<PF3>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Sess0.Screen.Sendkeys("<PF3>")    
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
       
        
         
Next X
End Sub

 

RE: Move to the next cell in the loop if blank data is pulled from Screen

something like this

CODE

if Trim(A2) = "" then goto here:



here:
next x

RE: Move to the next cell in the loop if blank data is pulled from Screen

(OP)
PERFECT!!    Thanks!!

RE: Move to the next cell in the loop if blank data is pulled from Screen

another way would have been to write it this way

CODE

if Trim(A2) <> "" then
'your script goes here
end if

in other words, if the cell is not blank, then do something

 

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