在 VBA 中使用 find 方法

Using the find method in VBA

提问by Felipe

I′m really interested in programming and decided to start with VBA, because of my work. So, I′m having some trouble with a code. I need to identify cells which formula results is TRUE, then clear contents of the first cell in the same row selected. But when I use loop, the macro returns the same result 3 times (which is the exact number o rows that must be changed). I′ll put my code below. Could someone give me some help?

由于我的工作，我对编程非常感兴趣并决定从 VBA 开始。所以，我在使用代码时遇到了一些麻烦。我需要确定公式结果为 TRUE 的单元格，然后清除所选同一行中第一个单元格的内容。但是当我使用循环时，宏返回相同的结果 3 次（这是必须更改的确切行数）。我会把我的代码放在下面。有人能给我一些帮助吗？

Thanks!!!

谢谢！！！

Sub Teste2sigma()

Windows("1.xls").Activate
    Sheets("Standard 1").Activate
    Range("AI3:AJ42").Select
    With Range("AI3:AJ42")
            Set C = .Find("TRUE", LookIn:=xlValues)
            If Not C Is Nothing Then
                ClearAddress = C.Address
                ClearRow = C.Row
                ClearColumn = C.Column
                Do
                    Cells(ClearRow, 1).Select
                    Cells(ClearRow, 1).ClearContents
                    ClearRows = ClearAddress & "," & C.Address(RowAbsolute:=False)
                    'Cells(ClearRow, ClearColumn).Select
                    Set C = .FindNext(After:=C)
                Loop While Not C Is Nothing And C.Address <> ClearAddress
            End If
        End With


End Sub

回答by chuff

As noted by Craig T, your clear action was misplaced. I've also streamlined the code a bit.

正如 Craig T 所指出的，你的明确行动是错误的。我还稍微简化了代码。

  Option Explicit
  Sub Teste2sigma()

  Dim c As Range
  Dim ClearAddress As String
  Dim ClearRow As Long

  With ThisWorkbook.Worksheets("Sheet1").Range("AI3:AJ42")
      Set c = .Find(What:=True, LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).ClearContents
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

  End Sub

回答by Craig T

Try putting the ClearRow assignment inside the loop.

尝试将 ClearRow 分配放入循环中。

Do
    ClearRow = C.Row
    Cells(ClearRow, 1).Select

At the moment you are assigning ClearRow prior to your loop, which mean you are clearing the contents of the same cell every time.

目前，您正在循环之前分配 ClearRow，这意味着您每次都在清除同一单元格的内容。

回答by Felipe

Thank you so much for the answers!! I′ve used the code written by chuff and it worked. However, this is was just part of a bigger code, which has to do the same thing with 7 sheets in many ordered woorkbooks (1 to n). So, I have repeated the code for each woorkbook but it doesn′t change nothing. One thing important is that "True" is the result of a OR function in excel.

非常感谢您的回答！！我使用了 chuff 编写的代码并且它有效。然而，这只是更大代码的一部分，它必须对许多有序的工作簿（1 到 n）中的 7 张纸做同样的事情。所以，我已经为每本工作簿重复了代码，但它并没有改变任何东西。重要的一件事是“True”是 excel 中 OR 函数的结果。

 Sub Teste2Sigma()


 Windows("datacao_v2.xls").Activate
        Sheets("SamList").Activate
        Range("f2").Select
        varPasta = ActiveCell
        varDatacao = varPasta & "datacao_v2"
        Sheets("SamList").Select
        Columns(1).Find(What:="fim").Activate
        fim = ActiveCell.Row 'linha correspondente ao "fim"
        first = Cells(4, 3) 
        aux = Range(Cells(fim - 3, 1), Cells(fim - 3 - 9, 1)).Find(What:="GJ", SearchDirection:=xlPrevious).Row
        last = Cells(aux + 1, 3) 
        nInt = (fim - (fim - aux) - 3) / (first + 2) 
        nVal = first * nInt + last 'número total de amostras lidas
        nPlan = Ceiling(nVal / 4)
        media = Range(Cells(2, 1), Cells(nPlan + 1, 1))


    For K = 1 To nPlan

        Windows(K & ".xls").Activate



    Dim c As Range
    Dim ClearAddress As String
    Dim ClearRow As Long

  With Worksheets("Standard 1").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

  With Worksheets("Standard 2").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

With Worksheets("Sample 1").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

With Worksheets("Sample 2").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

With Worksheets("Sample 3").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

With Worksheets("Sample 4").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

        With Worksheets("Blank").Range("Z7:Z46")
            Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
             If Not c Is Nothing Then
                 ClearAddress = c.Address
                 Do
                  ClearRow = c.Row
                    Cells(ClearRow, 1).Value = ""
                        Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
             End If
  End With

        With Worksheets("Blank").Range("AA7:AA46")
            Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
            If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 23).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

Next K


End Sub

回答by Felipe

Thank you guys, now everything is fine!!! Below the final code

谢谢你们，现在一切都很好！！！在最终代码下方

The problem is that c variable had to be clean before start the next "with". So I′ve inserted Set c = Nothingbefore every "with", like this:

问题是 c 变量在开始下一个“with”之前必须是干净的。所以我Set c = Nothing在每个“with”之前插入 ，像这样：

Set c = Nothing

    Worksheets("Sample 2").Activate

With Worksheets("Sample 2").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With

    Set c = Nothing

    Worksheets("Sample 3").Activate

With Worksheets("Sample 3").Range("AI3:AJ42")
      Set c = .Find(What:="True", LookIn:=xlValues, lookat:=xlPart)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Cells(ClearRow, 1).Value = ""
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If
  End With