For Loop - For each return nothing

Question

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.

thank you.

`Range("D" & ActiveCell.row)` will return 1 cell, so it's not really going to be looping thru anything. I doubt that is your intent. — braX, Apr 16 '23 at 05:23
https://stackoverflow.com/questions/62710000/looping-through-the-rows-until-last-row — braX, Apr 16 '23 at 05:44

Tim Williams · Answer 1 · 2023-04-17T15:42:17.820

1

EDIT - updated to loop all values in Col D, starting at D6

This should work:

Dim c As Range, ws As Worksheet

Set ws = ActiveSheet

'loop from D6 to last cell in ColD with a value
For Each c In ws.Range("D6:D" & ws.Cells(Rows.Count, "D").End(xlUp).Row).Cells
    If Len(c.value) > 0 Then        'if there's a value...
        
        With session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]")
            .Text = c.value
            .caretPosition = 9
        End With
        session.findById("wnd[0]").sendVKey 0

    End If
Next c

edited Apr 17 '23 at 15:42

answered Apr 16 '23 at 06:23

Tim Williams

154,628
8
97
125

Hi Tim, this worked perfectly but how can i make it if there’s another looping but i doesnt use d6 anymore, as you can see in the image, there’s blank row and after blank, there’s new set of data, and the data need to be looping for next one… i’ve provided my full code and sorry for my broken english – sephiroth Apr 16 '23 at 08:35

score 0 · Answer 2 · answered Apr 16 '23 at 08:33

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.

Hi kevin, i’ve updated and provided the full code, yes after it reach the blank cells/row, it will run another loop and select the new cells after the blank. — sephiroth, Apr 16 '23 at 08:37
The code I posted will continue to row 13. It isn't "running another loop", it's all just one loop. Just replace your code from "For Each cell2 In Range("D" & ActiveCell.row)" to "Next cell2" with the code I provided, it looks like it should work. — kevin, Apr 16 '23 at 09:17

For Loop - For each return nothing

2 Answers2