如果单元格包含某个字符串，则VBA会从电子表格中删除行

I am very new to VBA. I am trying to isolate a particular customers transactions on a spread sheet.

我对VBA很新。我试图在电子表格中隔离特定的客户交易。

Column "A" contains the Customer payment method (prepaid or various other methods). Column "D" contains the customer we are shipping too, Column "L" contains the customer we are shipping from.

“A”列包含客户付款方式(预付款或其他各种方法)。列“D”包含我们正在发货的客户,列“L”包含我们从中发货的客户。

If the cell in column "A" has 'prepaid' I want to search column "D" for the customer name (jaba). I will then delete all rows which do not contain this customer. IF the cell in column "A" is not 'prepaid' I want it to search column "L" for the customer name, and delete all rows which do not contain this string. When I run the code provided below I get an 1004 error on the following script 'If cell3.Find(ContainWord) Is Nothing Then'. Any help would be much appreciated.

如果“A”列中的单元格具有“预付费”,我想在列“D”中搜索客户名称(jaba)。然后,我将删除所有不包含此客户的行。如果列“A”中的单元格不是“预付”,我希望它在列“L”中搜索客户名称,并删除所有不包含该字符串的行。当我运行下面提供的代码时,我在以下脚本上得到1004错误'如果cell3.Find(ContainWord)是Nothing Then'。任何帮助将非常感激。

Sub DoNotContainClearCells()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range

Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range
Dim ContainWord As String

'What range do you want to search?
  Set rng = Range("A:A")
  Set rng2 = Range("D:D")
  Set rng3 = Range("L:L")
  'What phrase do you want to test for?
  ContainWord = "jaba"

For Each cell In rng

    If cell.Value = "prepaid" Then


        'Loop through each cell in range and test cell contents
        For Each cell2 In rng2.Cells
            If cell2.Find(ContainWord) Is Nothing Then EntireRow.Delete
        Next cell2

        Else
        'Loop through each cell in range and test cell contents
        For Each cell3 In rng3.Cells
            If cell3.Find(ContainWord) Is Nothing Then EntireRow.Delete
        Next cell3

    End If
Next
End Sub

1 个解决方案

#1

Without discussing the logic of your code, which is not part of the question, I can tell you that the

如果不讨论代码的逻辑,这不是问题的一部分,我可以告诉你

Run-time error '1004': Object required

运行时错误'1004':需要对象

in this line:

在这一行:

If cell3.Find(ContainWord) Is Nothing Then EntireRow.Delete

is because the EntireRow.Delete is missing a range (see Range.EntireRow Property (Excel))

是因为EntireRow.Delete缺少一个范围(请参阅Range.EntireRow属性(Excel))

Solution: Replace these lines:

解决方案:替换这些行:

If cell2.Find(ContainWord) Is Nothing Then EntireRow.Delete
If cell3.Find(ContainWord) Is Nothing Then EntireRow.Delete

with these:

If cell2.Find(ContainWord) Is Nothing Then cell2.EntireRow.Delete
If cell3.Find(ContainWord) Is Nothing Then cell3.EntireRow.Delete

Suggest to always have at the top of the modules\classes\userforms:

建议始终位于modules \ classes \ userforms的顶部:

Option Explicit

This would have given a Compile error: Variable not defined highlighting the error earlier at compiling time (see Option Explicit Statement)

这会产生一个Compile错误:未定义变量,在编译时更早地突出显示错误(参见Option Explicit Statement)

#1