Help readjusting format and order of loops to flow differently

[mnty89]

This might be a little complex or abstract to understand without seeing or if the process is unfamiliar but I'm stumped so I have to ask..

To explain a little easier it really is just a looping order issue, and how to pick up where I want it to.

The input spreadsheet has a value in column 1 which is variable arrtowd(i, 1) in the code, and another one in column 3, arrtowd(i, 3)


The way it works currently/below- it treats every individual row independently. It will loop over the script for each row, then repeat at the next row. The issue with this is efficiency. The last part that saves is time consuming so the rework I want is to basically say, if the line below the current arrtowd variable is the same, then do another loop over a part of the macro that uses the arrtwod(i, 3) variable, and then when it's finally different a row down, then end/save, and then start again, but not at the next row, but the row where the first column value stopped. so if the same value was in rows 2-4, then after looping I would want it to start at 5.

I'm confusing myself even trying to explain this honestly, but if someone does understand this then you'll be my hero. This is what happens when you have just base understanding of vba and try to tweak something someone else created quite extensively. It works, but is very inefficient.



Sub DeleteItems()


'Dim bOL As String
'Set wb = ThisWorkbook


Set ws = Sheets("Input SOs")




SessionCheck = "SESSION_MANAGER"
SAPConnect




Session.findById("wnd[0]/tbar[0]/okcd").Text = "VA02"
Session.findById("wnd[0]/tbar[0]/btn[0]").press


ws.Activate


Application.EnableEvents = False
Application.CutCopyMode = False
Rows("1:1").Insert Shift:=xlDown
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
intRows = ws.UsedRange.Rows.Count
intCols = 7
ReDim Preserve arrTwoD(1 To intRows, 1 To intCols)
For i = 1 To UBound(arrTwoD, 1)
For j = 1 To UBound(arrTwoD, 2)
arrTwoD(i, j) = ws.Cells(i, j)
Next
Next


For i = 2 To intRows
pctCompl = (i - 1) / (intRows - 1)
progress pctCompl


' row X and sales order arrTwoD(i, 1)
'row X and item number

Session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = arrTwoD(i, 1)
Session.findById("wnd[0]/usr/btnBT_SUCH").press




If Session.ActiveWindow.Name = "wnd[1]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[1]"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Loop
End If
End If
If Session.ActiveWindow.Name = "wnd[2]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[2]"
Session.findById("wnd[2]/tbar[0]/btn[0]").press
Loop
End If
End If


On Error GoTo ErrorHandler






'Cancel line item part using GTIN/UCC
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/subSUBSCREEN_BUTTONS:SAPMV45A:4050/btnBT_POPO").press


Session.findById("wnd[1]/usr/ctxtRV45A-PO_MATNR").Text = arrTwoD(i, 3)
Session.findById("wnd[1]/usr/ctxtRV45A-PO_MATNR").SetFocus
Session.findById("wnd[1]/usr/ctxtRV45A-PO_MATNR").caretPosition = 8


'Session.findById("wnd[1]/usr/txtRV45A-PO_KDMAT").caretPosition = 6
Session.findById("wnd[1]/tbar[0]/btn[0]").press


'12 needs to be the column of the reason for rejection


'm version
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/cmbVBAP-ABGRU[12,0]").Key = "A1"
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/cmbVBAP-ABGRU[12,0]").SetFocus






'end cancel line item part


'need to add save then status etc.
'then next i
'then done and close up






'selects internal order text and writes in comment


'naviagte to backscreens
Session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/btnBT_HEAD").press
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08").Select




'enter texts


Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").selectItem "Z045", "Column1"
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").ensureVisibleHorizontalItem "Z045", "Column1"
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[0]/shell").doubleClickItem "Z045", "Column1"
ftext = Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[1]/shell").Text
Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\08/ssubSUBSCREEN_BODY:SAPMV45A:4152/subSUBSCREEN_TEXT:SAPLV70T:2100/cntlSPLITTER_CONTAINER/shellcont/shellcont/shell/shellcont[1]/shell").Text = ftext + vbCr + ws.Cells(i, 5)






'save order
Session.findById("wnd[0]/tbar[0]/btn[11]").press


If Session.ActiveWindow.Name = "wnd[2]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[2]"
Session.findById("wnd[2]/tbar[0]/btn[0]").press
Loop
End If
End If


If Session.ActiveWindow.Name = "wnd[1]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[1]"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Loop
Else:
Session.findById("wnd[1]/usr/btnBUTTON_2").press
End If
End If






'starts from fresh order entry so can loop without error on invalid
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/NVA02"
Session.findById("wnd[0]/tbar[0]/btn[0]").press




If Session.ActiveWindow.Name = "wnd[2]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[2]"
Session.findById("wnd[2]/tbar[0]/btn[0]").press
Loop
End If
End If


If Session.ActiveWindow.Name = "wnd[1]" Then
If Session.ActiveWindow.Text = "Information" Then
Do Until Session.ActiveWindow.Name <> "wnd[1]"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Loop
Else:
Session.findById("wnd[1]/usr/btnBUTTON_2").press
End If
End If










LoopEndScript

Next i


EndSapConnection
ws.Range("A1").Select
MsgBox ("Lines for specified items have been A1-ed." _
& vbCrLf & "Please check lines with errors to make sure the Sales Order number/info is correct.")


Columns("F:F").Select
Columns("F:F").EntireColumn.AutoFit
Range("A1").Select




Exit Sub


ErrorHandler:
ws.Cells(i, 6) = "Verify Sales Order Number, and inputted information."
DoEvents
Resume Next


End Sub


Sub progress(pctCompl As Single)


ProgressBarF.Text.Caption = Format(pctCompl, "0%") & " Processed"
ProgressBarF.Line.Caption = "Line " & i - 1 & " of " & intRows - 1
ProgressBarF.Bar.Width = pctCompl * ProgressBarF.Width
DoEvents
Sheets("Input SOs").Select
Range("A1").Select


End Sub




Private Sub LoopEndScript()


If ws.Cells(i, 6) = "" Then
ws.Cells(i, 6) = "Done"
Else: 'blank to keep error message
End If
End Sub