400错误Excel宏 [英] 400 Error Excel Macro

问题描述

我正在尝试运行一个宏，它将删除列B中不包含特定值的行。这是我的代码：

  Sub deleteRows（）
 Dim count As Integer 
 count = Application.WorksheetFunction.CountA（Range（AF：AF））
 Dim i As Integer 
i = 21 
尽管i <= count 
如果（Application.WorksheetFunction.IsNumber（Application.WorksheetFunction.Search（OSR Platform，Range（B& i）））= False）然后
如果（Application.WorksheetFunction.IsNumber（Application.WorksheetFunction.Search（IAM，Range（B& i）））= False）然后
 Rows（i）.EntireRow.Delete 
i = i  -  1 
 count = count  -  1 
如果
结束If如果
i = i + 1 
循环
 End Sub 
  

现在应该做的是以下内容：

1））查找要执行的行数，并将其设置为count（t他的作品）

2。）从第21行开始，在列B中查找OSR平台和IAM[这种作品（见下文）] / p>

3。）如果找不到，删除整个行，并根据需要调整计数和行号（这样）

由于某种原因，每当代码进入第一个If语句时，出现一个红色X的错误窗口弹出，只显示400。据我所知，我已经在语法上写得好一些，但是显然有些错误。

解决方案

你可能想要开始通过循环的方式。当您删除一行时，所有以前的行将被移动。你解释了这一点，但是反向循环对于我来说更为简单（对我而言），而不是跟踪当我在循环中抵消当前位置时：

对于i = count到21步骤-1

另外，你也依赖很多在 Application.WorksheetFunction ：

（Application.WorksheetFunction.IsNumber（Application.WorksheetFunction .Search（OSR Platform，Range（B& i）））= False）

to

InStr（Range（B& i）.value，OSR Platform）> 0

Application.WorksheetFunction 需要更多的处理能力，取决于你正在努力完成，这可能需要更长的时间。同样对于这个建议的改变，代码大小减少，变得更容易阅读没有它。

您的计数也可以在没有 A.WF ：

• Excel 2000/03： count = Range（AF65536）。End（xlUp）


• Excel 2007/10： count = Range（AF1048576）。End（xlUp）.Row


• 独立版本： count =范围（AF& Rows.Count）.End（xlUp）.Row li>
  

另外还有一件事就是你可以做（​​在这种情况下，应该） $ c>如果语句成一个。

进行这些更改，最终得到：

  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 
如果Len（Range（B& i）.value）> 0然后
如果InStr（Range（B& i）.value，OSR Platform）> 0或InStr（Range（B& i）.value，IAM）> 0然后
范围（B& i）.Interior.Color = RGB（255，0，0）
结束如果
结束如果
下一个i 
 End Sub 
  

如果这没有帮助，那么你可以一行一步地浏览代码。添加一个断点，并使用 F8 。突出显示代码中的变量，右键单击，选择添加Watch ...，单击确定，（这是一个很好的资源，可以帮助您进行调试），并注意以下内容：

• 哪一行命中错误？


• 发生这种情况时， i 和 count 的值是多少？ （添加这些变量的手表来帮助）

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:

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)

2.) Start at row 21 and look for "OSR Platform" and "IAM" in column B [this kind of works (see below)]

3.) If it finds neither, delete the entire row and adjust the count and row number as necessary (this works)

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.

解决方案

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

Also, you're relying too much on Application.WorksheetFunction:

(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False)

to

InStr(Range("B" & i).value, "OSR Platform") > 0

Application.WorksheetFunction takes 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.

Your count can also be obtained without 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

One more thing is that you can do (and should do in this case) is combine your If statements into one.

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:

• Which line hits the error?
• What is the value of i and count when that happens? (add a watch on these variables to help)

这篇关于400错误Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！