Having issue in setting-up a "For Loop" code

[Macro_Maniac]

Dear experts,

Am trying to build a small utility which extracts the status from a mainframe application..
This is the first time am attempting to write a VBA macro, so pls help.

Managed to write a code as below and this works fine for the first record where,
1. It picks the REF,
2. inputs in the application,
3. retrieves the status and
4. paste it in the excel.

However the problem is, it doesn't move to next ref in col D and I want it to continue above 4 steps utill it get hits by a blank cell in col D

==========================
Sub REF()
Dim Srow As Double
Dim rowindex As Integer
Dim REFIndex As Integer
Dim SCRowIndex As Integer
Dim REF As Variant

'ref is available from row 3, in col D
rowindex = 3
REFIndex = 4
Srow = 3
Do
Application.ScreenUpdating = False
Sheets("sheet1").Activate
'reference are maintained in col D
For Each REF In Sheets("sheet1").Range("D:D")
If REF = "" Then
End If

' trim REF and then copy ref.
REF = Trim(Cells(rowindex, REFIndex))
Sheets("sheet1").Cells(1 + Srow, 4).Copy

'Paste REF in application anc check the status
autECLOIA.WaitForAppAvailable
autECLOIA.WaitForInputReady
autECLPS.SetCursorPos 21, 13
autECLPS.SendKeys (REF)
autECLPS.SendKeys "[enter]"

'get the status from the screen and update in excel col F (same row, col D from where REF was picked)
If autECLPS.GetText(1, 2, 6) = "MI031" Then
Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status
End If
autECLPS.SendKeys "[enter]"
autECLOIA.WaitForAppAvailable
autECLOIA.WaitForInputReady
Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status

Next REF
Exit Do
Loop
End Sub
==========================

 

[skywriter]

You would use something like this to just move from cell to cell until you ended up with a blank cell.

Dim r As Long   ' this would be for the row.
r = 3

Do Until Cells(r, 4).Value = ""

Cells(r, 4).Select ' do something here select, get the value whatever.

r = r + 1

Loop

 

[Macro_Maniac]

Dear Master Bruce,

Thank you for your inputs..

have tried it but it didnt work.. perhaps am worng somewhere

Sub REF ()
Dim Srow As Double
Dim rowindex As Integer
Dim REFIndex As Integer
Dim SCRowIndex As Integer
Dim REF As Variant
'ref is available from row 3, in col D
rowindex = 3
REFIndex = 4
Srow = 3

Do
    Application.ScreenUpdating = False
    Sheets("sheet1").Activate
    'reference are maintained in col D
    For Each REF In Sheets("sheet1").Range("D3:D10")
    ' If REF = "" Then
    ' End If
Dim r As Long   ' this would be for the row.
r = 3
Do Until Cells(r, 4).Value = ""

    ' trim REF and then copy ref.
    REF = Trim(Cells(rowindex, REFIndex))
    'Sheets("sheet1").Cells(1 + Srow, 4).Copy
Cells(r, 4).Select ' do something here select, get the value whatever.

    'Paste REF in application anc check the status
    autECLOIA.WaitForAppAvailable
    autECLOIA.WaitForInputReady
    autECLPS.SetCursorPos 21, 13
    autECLPS.SendKeys (REF)
    autECLPS.SendKeys "[enter]"
    
    'get the status from the screen and update in excel col F (same row, col D from where REF was picked)
    If autECLPS.GetText(1, 2, 6) = "MI031" Then
    Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status
    End If
    autECLPS.SendKeys "[enter]"
    autECLOIA.WaitForAppAvailable
    autECLOIA.WaitForInputReady
    Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status
 
r = r + 1
Loop
Next REF
Exit Do
Loop
End Sub

 

[skywriter]

This code will loop through the cells in the column D starting on row 3 and moving down. As it loops through you can use the current cell that it is looping through to get a value if you wish. Here is an example I changed one of your lines to REF = Trim(Cells(r, 4)).Value so the first time through the loop REF will be given the value in cells(3,4) which is cell D4, your values srow rowindex etc will not change unless you give them an initial value before the loop starts and then at the bottom of the loop you can add +1 to the value and on the next run through the value will be increased by one.

I don't know how much more I can help you without having access to the file because I'm not sure what you are doing and some of the stuff in this code like autECLOIA.WaitForAppAvailable, I don't even know what that is. So understand with my code I gave r a value of 3 before the loop so the first time through the loop r has a value of three then I add one at the bottom just before the loop statement the it loops again etc.

Sub TestMynumber()

Debug.Print Evaluate(Sheet1.Names("mynumber").Value)
Range("B4").Formula = "=mynumber"


End Sub
ub REF()
Dim Srow As Double
Dim rowindex As Integer
Dim REFIndex As Integer
Dim SCRowIndex As Integer
Dim REF As Variant
'ref is available from row 3, in col D
rowindex = 3
REFIndex = 4
Srow = 3

    Application.ScreenUpdating = False
    Sheets("sheet1").Activate
    'reference are maintained in col D
    ' If REF = "" Then
    ' End If
Dim r As Long   ' this would be for the row.
r = 3

Do Until Cells(r, 4).Value = ""

    ' trim REF and then copy ref.
    REF = Trim(Cells(r, 4)).Value
    'Sheets("sheet1").Cells(1 + Srow, 4).Copy

    'Paste REF in application anc check the status
    autECLOIA.WaitForAppAvailable
    autECLOIA.WaitForInputReady
    autECLPS.SetCursorPos 21, 13
    autECLPS.SendKeys (REF)
    autECLPS.SendKeys "[enter]"
    
    'get the status from the screen and update in excel col F (same row, col D from where REF was picked)
    If autECLPS.GetText(1, 2, 6) = "MI031" Then
    Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status
    End If
    autECLPS.SendKeys "[enter]"
    autECLOIA.WaitForAppAvailable
    autECLOIA.WaitForInputReady
    Cells(Srow, "f").Value = autECLPS.GetText(5, 28, 5) 'Status
 
r = r + 1

Loop

Application.ScreenUpdating = True

End Sub

 

[Macro_Maniac]

Dear Master Bruce,

Cheers, we made it!
With a bit of tweaking i.e. on trim code and changed the r to srow as same ref is being used in the subsequent code..

Reg. your question on "autECLOIA" actually this is realted to mainframe application, kind of command to move the screen using macro.

Thanks a ton.. without your help this would have not been possible..

Cheers.

 

[skywriter]

Dear Master Bruce,

Cheers, we made it!
With a bit of tweaking i.e. on trim code and changed the r to srow as same ref is being used in the subsequent code..

Reg. your question on "autECLOIA" actually this is realted to mainframe application, kind of command to move the screen using macro.

Thanks a ton.. without your help this would have not been possible..

Cheers.

I'm glad it worked out.
Thanks for the feedback, my pleasure.