Using the find method in VBA

Question

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?

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

chuff · Accepted Answer · 2013-06-04 12:06:32Z

3

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

  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

edited Jun 4, 2013 at 12:06

answered Jun 4, 2013 at 1:00

chuff

5,9061 gold badge23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JosieP Over a year ago

btw the Loop line ought to be Loop While c.Address <> ClearAddress because c can't be Nothing here (already tested) and if it were the line would error due to trying to test its Address property

Craig T · Accepted Answer · 2013-06-04 00:49:55Z

2

Try putting the ClearRow assignment inside the loop.

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.

answered Jun 4, 2013 at 0:49

Craig T

2,7725 gold badges25 silver badges34 bronze badges

Comments

Felipe · Accepted Answer · 2013-06-04 14:16:42Z

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.

 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

Another difference is about What:="True" and Cells(ClearRow, 1).ClearContents. Using the original code (What:="True") by chuff, some cells that shouldn´t be cleared were. And using .Clearcontents created a problem with merged cells.

Felipe · Accepted Answer · 2013-06-05 11:37:40Z

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 = Nothing before every "with", like this:

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

Collectives™ on Stack Overflow

Using the find method in VBA

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related