提问人：Sidvi 提问时间：11/27/2017 最后编辑：Sandra RossiSidvi 更新时间：6/21/2023 访问量：73
Excel 读取没有数据的行，然后跳过此行
Excel reading lines with no data and then jump over this line
问：
我的代码有问题。我需要它来读取成本中心和物料编号，并在 SAP 系统中查找它们。它完美地做到了这一点。问题是用户应该能够手动输入物料和价格，也没有成本中心和物料编号。
程序应该看到这一行，然后转到下一行。现在，当它遇到空行时，它会停止从 SAP 系统加载信息。我知道它为什么会这样做，但我似乎不知道如何纠正它。您可以看到下面的代码。问题出在“While”语句上。
Sub StartExtract()
Dim currentline As Integer
' This is the system to connect to
W_System = "PE1400"
' We start looking for order numbers from line 6 in the sheet
currentline = 6
While Cells(currentline, 1).Value <> ""
' Run the actual GUI script
RunGUIScript currentline
' move to the next line
currentline = currentline + 1
Wend
End Sub
答：
-1赞
Daniel Möller
11/27/2017
#1
您可以将 while 替换为一直到最后一行填充的 a：for
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
for currentline = 6 to LastRow
'if Cells(currentline,1).value<>"" then
RunGUIScript currentline
'end if
'the for will automatically increment currentline
Next
0赞
Jean-Pierre Oosthuizen
11/27/2017
#2
试试这个。
如果您有任何问题，请告诉我。
Option Explicit
Sub StartExtract()
'You should really reference the Workbook and Sheet..ALWAYS
Dim DataWS As Worksheet
'Please insert the sheet name in which the data lies
Set DataWS = ThisWorkbook.Sheets("Sheet1")
Dim LastRow As Long
With DataWS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' This is the system to connect to
W_System = "PE1400"
' We start looking for order numbers from line 6 in the sheet
'Always want to use Long when referencing Columns or Rows
Dim currentline As Long
For currentline = 6 To LastRow
If DataWS.Cells(currentline, 1).Value <> "" Then
' Run the actual GUI script
RunGUIScript currentline
End If
Next currentline
End Sub
0赞
Sidvi
11/27/2017
#3
谢谢你@Zerk！ 我尝试重写我的代码以符合您的建议，这最终奏效了：
Sub StartExtract()
Dim currentline As Integer
Dim i As Long
' This is the system to connect to
W_System = "PE1400"
' We start looking for order numbers from line 6 in the sheet
currentline = 6
For i = 6 To 300
If Cells(currentline, 1).Value <> "" Then
' Run the actual GUI script
RunGUIScript currentline
' move to the next line
currentline = currentline + 1
Else
currentline = currentline + 1
End If
Next i
End Sub
评论
for x = min to maxif cells(x,1) <> "" then