Excel VBA循环单元格写入SAP时For Each循环无返回值问题求助

问题概述 #

使用Excel VBA循环单元格并将值填充到SAP时，单元格无返回值。核心问题出在For Each cell2 In Range("D" & ActiveCell.row)循环处：

• 执行cell2.Offset(1, 0).Select选中下一行后，cell2无法识别该行的新值
• 无法使用固定范围（如Range("D6")）执行循环，因为后续循环需要处理空白单元格后的新数据

原代码 #

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

解决方案 #

核心循环修复 #

原循环的问题在于Range("D" & ActiveCell.row)仅指向单个单元格，For Each只会执行一次，且Select操作无法改变cell2的引用。改用行号遍历+Do While循环，直接基于单元格值判断终止条件：

'Loop Document number
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Auto Post")
Dim currentRow As Long
currentRow = ActiveCell.Row

Do While Not ws.Cells(currentRow, "D").Value = ""
    ' 获取当前行D列的文档编号
    Dim docNum As String
    docNum = ws.Cells(currentRow, "D").Value
    
    ' 填充SAP字段
    session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").Text = docNum
    session.findById("wnd[0]/usr/sub:SAPMF05A:0731/txtRF05A-SEL01[0,0]").caretPosition = 9
    session.findById("wnd[0]").sendVKey 0
    
    ' 行号自增，处理下一行
    currentRow = currentRow + 1
Loop
nextstep: ' 保留原跳转标签

额外优化：避免Select/ActiveCell依赖 #

原代码大量使用Select和ActiveCell，容易导致引用混乱，建议改为直接引用工作表和单元格：

' 替换原Select相关代码
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Auto Post")
Dim targetRow As Long
targetRow = cell.Row

' 找到I列向上的目标行
Dim iRange As Range
Set iRange = ws.Cells(targetRow, "I").End(xlUp)
If iRange.Value = "Amount in doc. curr." Then
    targetRow = iRange.Offset(1, 0).Row
Else
    targetRow = iRange.Row
End If

' 直接获取值，无需Select
Payer = ws.Cells(targetRow, "B").Value

内容的提问来源于stack exchange，提问作者sephiroth