vba 400 错误 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11615082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
400 Error Excel Macro
提问by jrad
I'm trying to run a macro that will delete rows that don't contain a particular value in column B. Here's my code:
我正在尝试运行一个宏，该宏将删除列 B 中不包含特定值的行。这是我的代码：
Sub deleteRows()
Dim count As Integer
count = Application.WorksheetFunction.CountA(Range("AF:AF"))
Dim i As Integer
i = 21
Do While i <= count
If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False) Then
If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("IAM", Range("B" & i))) = False) Then
Rows(i).EntireRow.Delete
i = i - 1
count = count - 1
End If
End If
i = i + 1
Loop
End Sub
Now what it SHOULD be doing is the following:
现在它应该做的是以下内容：
1.) Find the number of rows to go through and set that as count (this works)
1.) 找到要经过的行数并将其设置为计数（这有效）
2.) Start at row 21 and look for "OSR Platform" and "IAM" in column B [this kind of works (see below)]
2.) 从第 21 行开始，在 B 列中寻找“OSR Platform”和“IAM”[这种作品（见下文）]
3.) If it finds neither, delete the entire row and adjust the count and row number as necessary (this works)
3.) 如果两者都没有找到，则删除整行并根据需要调整计数和行号（这有效）
For some reason, whenever the code gets to the first If statement, an error window with a red X pops up that just says "400." As far as I can tell, I have written everything syntactically soundly, but clearly there's something wrong.
出于某种原因，每当代码到达第一个 If 语句时，就会弹出一个带有红色 X 的错误窗口，上面写着“400”。据我所知，我在语法上写得很好，但显然有一些问题。
回答by Gaffi
You may want to start by looping the other way. When you delete a line, all the previous lines are shifted. You account for this, but a reverse loop is simpler (for me anyways) to understand than keeping track of when I've offset the current position within the loop:
您可能希望以另一种方式循环开始。当您删除一行时，所有先前的行都将被移动。您考虑到了这一点，但反向循环比跟踪我何时偏移循环中的当前位置更容易理解（无论如何对我来说）：
For i = count To 21 Step -1
For i = count To 21 Step -1
Also, you're relying too much on Application.WorksheetFunction:
此外，您过于依赖Application.WorksheetFunction：
(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False)
(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False)
to
到
InStr(Range("B" & i).value, "OSR Platform") > 0
InStr(Range("B" & i).value, "OSR Platform") > 0
Application.WorksheetFunctiontakes much more processing power, and depending on what you are trying to accomplish, this can take a significantly longer amount of time. Also for this suggested change, the code size is reduced and becomes easier to read without it.
Application.WorksheetFunction需要更多的处理能力，并且根据您要完成的任务，这可能需要更长的时间。同样对于这个建议的更改，代码大小减少并且没有它变得更容易阅读。
Your countcan also be obtained without A.WF:
您count也可以在没有A.WF：
- Excel 2000/03:
count = Range("AF65536").End(xlUp).Row - Excel 2007/10:
count = Range("AF1048576").End(xlUp).Row - Version independent:
count = Range("AF" & Rows.Count).End(xlUp).Row
- Excel 2000/03：
count = Range("AF65536").End(xlUp).Row - Excel 2007/10：
count = Range("AF1048576").End(xlUp).Row - 版本无关：
count = Range("AF" & Rows.Count).End(xlUp).Row
One more thing is that you can do (and shoulddo in this case) is combine your Ifstatements into one.
还有一件事是你可以做的（在这种情况下应该做的）是将你的If陈述合二为一。
Making these changes, you end up with:
进行这些更改，您最终会得到：
Sub deleteRows()
Dim count As Integer
count = Range("AF" & Rows.Count).End(xlUp).Row
Dim i As Integer
For i = count To 21 Step -1
If Len(Range("B" & i).value) > 0 Then
If InStr(Range("B" & i).value, "OSR Platform") > 0 Or InStr(Range("B" & i).value, "IAM") > 0 Then
Range("B" & i).Interior.Color = RGB(255, 0, 0)
End If
End If
Next i
End Sub
If this does not help, then can you step through the code line by line. Add a breakpoint, and step through with F8. Highlight the variables in your code, right-click, choose "add Watch...", click "OK", (Here's an excellent resource to help you with your debugging in general) and note the following:
如果这没有帮助，那么您可以逐行逐行执行代码。添加断点，然后使用F8. 突出显示代码中的变量，右键单击，选择“添加监视...”，单击“确定”，（这是一个很好的资源，可以帮助您进行一般的调试）并注意以下几点：
- Which line hits the error?
- What is the value of
iandcountwhen that happens? (add a watch on these variables to help)
- 哪一行遇到错误？
- 什么是价值
i以及count何时发生？（在这些变量上添加监视以提供帮助）
回答by JimmyPena
This worked for me. It uses AutoFilter, does not require looping or worksheet functions.
这对我有用。它使用自动筛选，不需要循环或工作表功能。
Sub DeleteRows()
Dim currentSheet As Excel.Worksheet
Dim rngfilter As Excel.Range
Dim lastrow As Long, lastcolumn As Long
Set currentSheet = ActiveSheet
' get range
lastrow = currentSheet.Cells(Excel.Rows.Count, "AF").End(xlUp).Row
lastcolumn = currentSheet.Cells(1, Excel.Columns.Count).End(xlToLeft).Column
Set rngfilter = currentSheet.Range("A1", currentSheet.Cells(lastrow, lastcolumn))
' filter by column B criteria
rngfilter.AutoFilter Field:=2, Criteria1:="<>*OSR Platform*", Operator:= _
xlAnd, Criteria2:="<>*IAM*"
' delete any visible row greater than row 21 which does not meet above criteria
rngfilter.Offset(21).SpecialCells(xlCellTypeVisible).EntireRow.Delete
' remove autofilter arrows
currentSheet.AutoFilterMode = False
End Sub
This code applies AutoFilter to column B to see which rows contain neither "OSR Platform" nor "IAM" in column B. Then it simply deletes the remaining rows greater than 21. Test it on a copy of your workbook first.
此代码将自动筛选应用于 B 列，以查看 B 列中哪些行既不包含“OSR 平台”也不包含“IAM”。然后它只是删除大于 21 的其余行。首先在工作簿的副本上对其进行测试。
With a huge nod to this OzGridthread, because I can never remember the proper syntax for selecting visible cells after filtering.
非常感谢这个 OzGrid线程，因为我永远记不起过滤后选择可见单元格的正确语法。
