Column Question

[vzachin]

Hi,

I am updating a mainframe application with Attachmate via Excel. My data begins in Column A5 and continues down the column until the last row. If there is additional data, it continues every 5th column, Column E5,I5,M5 until Y5.
My coding basically copies each cell in Column A to the mainframe, does an update,and then continues down to the next cell and so forth.
The following code works but I stopped with Column E because there must be an easier method of writing this.
How can I re-write the coding so that it will loop every 5th column until there is no data in row 5 of that column?

Sub Overflow5()
        Dim Sessions As Object
        Dim System As Object
        Dim Sess0 As Object
        Set System = CreateObject("EXTRA.System")
        Set Sessions = System.Sessions
        Set Sess0 = System.ActiveSession
        Dim Rw As Long

        Sheets("Sheet1").Select
        For i = 5 To ActiveSheet.Rows.Count
        
        If Range("A" & i).FormulaR1C1 = "" Then
        For j = 5 To ActiveSheet.Rows.Count
        
        If Range("E" & j).FormulaR1C1 = "" Then
        For k = 5 To ActiveSheet.Rows.Count
        
        If Range("I" & k).FormulaR1C1 = "" Then
        
        Exit Sub
        
        End If
        Location = Range("I" & k)
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.SendKeys ("<PF5>")         'Update MainFrame
        Next k
        
        End If
        Location = Range("E" & j)
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.SendKeys ("<PF5>")         'Update MainFrame
        Next j
        
        End If
        Location = Range("A" & i)
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.SendKeys ("<PF5>")         'Update MainFrame
        Next i
        
End Sub

thanks
zach

 

[Fenrirshowl]

How about:

Sub Overflow5()
Dim Sessions As Object
Dim System As Object
Dim Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Dim Rw As Long
For x = 5 To 65536
    For y = 0 To 50
        If Cells(x, 5 * y + 1) = "" Then Exit Sub
        Location = Cells(x, 5 * y + 1).Address
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.SendKeys ("<PF5>")
    Next y
Next x
End Sub

Be aware that Location is defined in $A$1 format

Fen

 

[vzachin]

hi Fen,

Thanks for your reply. My data goes down the column first rather than going across.
How can I modify your coding so that it would go down first as opposed to going across?
so far i modified the following:
from:
[/code]
Location = Cells(x, 5 * y + 1).Address
[/code]

to:

Location = Cells(x, 4 * y + 1).value
[code]

thanks again
zach

 

[DirkStruan]

Zach

Looks like a slight oversight from Fen - just switch the loops:

Sub Overflow5()
Dim Sessions As Object
Dim System As Object
Dim Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Dim Rw As Long
For y = 0 To 50
    For x = 5 To 65536
        If Cells(x, 5 * y + 1) = "" Then Exit Sub
        Location = Cells(x, 5 * y + 1).value
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.SendKeys ("<PF5>")
    Next x
Next y
End Sub

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[vzachin]

hi Dirk & Fen,

That works!
one final question:
if i needed 2 columns at a time Col A&B, then Col E&F, then I&J, can this be done? where Col B,F & J would be Sess0.Screen.PutString Lead, 4, 11



thanks again
zach

 

[vzachin]

got it, guys! thanks again!

Sub Overflow5()
Dim Sessions As Object
Dim System As Object
Dim Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Dim Rw As Long
For y = 0 To 50
    For x = 5 To 65536
        If Cells(x, 4 * y + 1) = "" Then Exit Sub
        Location = Cells(x, 4 * y + 1).Value
        Lead = Cells(x, 4 * y + 2).Value
        Sess0.Screen.PutString Location, 2, 11
        Sess0.Screen.PutString Lead, 4, 11
        Sess0.Screen.SendKeys ("<PF5>")
    Next x
Next y
End Sub