Search code examples
excelvbaloopsforeachrow

For Loop - For each return nothing

I've an issue with Looping through cells in excel where i have to fill each cell value into SAP,

however i couldn't make it work because at the end the cells return as nothing.

here is my code

  Sub ClearingTest()

 

If Not IsObject(SAPApp) Then

   Set SapGuiAuto = GetObject("SAPGUI")

   Set SAPApp = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(SAPConnection) Then

   Set SAPCon = SAPApp.Children(0)

End If

If Not IsObject(session) Then

   Set session = SAPCon.Children(0)

End If

If IsObject(WScript) Then

   WScript.ConnectObject session, "on"

   WScript.ConnectObject SAPApp, "on"

   End If

  

LastRow = ThisWorkbook.Sheets("Auto Post").cells(Rows.Count, "N").End(xlUp).row

Dim rng As Range

Dim cell As Range

Dim cell2 As Range

 

For Each cell In Range("N:N")

    If (cell.Value = "ZERO BALANCE" Or cell.Value = "SHORTPAYMENT" Or cell.Value = "ON ACCOUNT" Or cell.Value = "WRITE OFF") Then

        cell.Offset(1).EntireRow.Insert

End If

LastRow = ThisWorkbook.Sheets("Auto Post").cells(Rows.Count, "N").End(xlUp).row

If cell.Value = "ZERO BALANCE" Then

cell.Select

Range("I" & ActiveCell.row).Select

If Selection.End(xlUp).Value = "Amount in doc. curr." Then

Selection.End(xlUp).Offset(1, 0).Select

ElseIf Not Selection.End(xlUp).Value = "Amount in doc. curr." Then

Selection.End(xlUp).Select

End If

Range("B" & ActiveCell.row).Select

 

CoCD = Worksheets("Auto Post").Range("C3").Value

PstDate = Worksheets("Auto Post").Range("D3").Value

PeriodYear = Worksheets("Auto Post").Range("E3").Value

PstKey = Worksheets("Auto Post").Range("D5").Value

Payer = Worksheets("Auto Post").Range("B" & ActiveCell.row).Value

Amount = Worksheets("Auto Post").Range("J5").Value

Curr = Worksheets("Auto Post").Range("F3").Value

Text = Worksheets("Auto Post").Range("L" & ActiveCell.row).Value

RCD = Worksheets("Auto Post").Range("N5").Value

 

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/NF-32"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[2,0]").Select

session.findById("wnd[0]/usr/ctxtRF05A-AGKON").Text = Payer

session.findById("wnd[0]/usr/ctxtBKPF-BUDAT").Text = PstDate

session.findById("wnd[0]/usr/txtBKPF-MONAT").Text = PeriodYear

session.findById("wnd[0]/usr/ctxtBKPF-BUKRS").Text = CoCD

session.findById("wnd[0]/usr/ctxtBKPF-WAERS").Text = Curr

session.findById("wnd[0]/usr/ctxtRF05A-AGUMS").Text = "OA"

session.findById("wnd[0]/usr/sub:SAPMF05A:0131/radRF05A-XPOS1[2,0]").SetFocus

session.findById("wnd[0]").sendVKey 2

session.findById("wnd[0]/tbar[1]/btn[7]").press

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/sub:SAPMF05A:0710/radRF05A-XPOS1[2,0]").Select

session.findById("wnd[0]/usr/ctxtRF05A-AGKON").Text = ""

session.findById("wnd[0]/usr/sub:SAPMF05A:0710/radRF05A-XPOS1[2,0]").SetFocus

session.findById("wnd[0]").sendVKey 2

 

'Loop Document number

 

For Each cell2 In Range("D" & ActiveCell.row)

If cell2 = Empty Then GoTo nextstep:

 

session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").Text = cell2

session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").caretPosition = 9

session.findById("wnd[0]").sendVKey 0

cell2.Offset(1, 0).Select

Next cell2

 

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[1]/btn[16]").press

session.findById("wnd[0]/usr/tabsTS/tabpREST").Select

session.findById("wnd[0]/mbar/menu[0]/menu[1]").Select

    

End If

Next cell

   

End Sub

The problem here is at For Each cell2 In Range("D" & ActiveCell.row) even after i select next row by using cell2.Offset(1, 0).Select, the cell2 didn't recognize the new value in next row.

also i can't use Range("D6") as for each in the loop due to when next looping, it will select new data after the blank cells as per below image.

enter image description here

thank you.

Solution

  • It isn't clear from your question what you want to do when you reach the blank rows 11 & 12. Do you want the macro to stop? Or do you want it to continue to row 13?

    As braX said above, Range("D" & ActiveCell.row) is just a single cell, so you cannot loop through it. You need to define the range of cells that you want to loop through. The below is probably what you want:

    Dim rFirst As Range, rLast As Range, rSource As Range, cell2 As Range

Set rFirst = Range("D" & ActiveCell.Row)    'First cell you want to copy to SAP. You can use Range("D6") if that will always be the first cell.
Set rLast = Range("D" & ActiveSheet.Rows.Count).End(xlUp)   'Last cell you want to copy to SAP  'This code will go to the last cell in column D
Set rSource = Range(rFirst, rLast)  'All the cells between the first and last cell. This is now a range you can loop through.

For Each cell2 In rSource

    If cell2 = Empty Then GoTo nextstep:

    session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").Text = cell2

    session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").caretPosition = 9

    session.findById("wnd[0]").sendVKey 0
       
    'cell2.Offset(1, 0).Select  'Not necessary to select each cell
    
Next cell2

    I've commented out the line where you select the next cell as it is probably not necessary. If you really need to select in Excel you can uncomment it, otherwise you can delete it.