CODEWITHSUNDEEP
excelvba
Felipe
Felipe

Reputation: 163

Using the find method in VBA

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

Upvotes: 2

Views: 17837

Answers (4)

Felipe
Felipe

Reputation: 163

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

Upvotes: 0

Felipe
Felipe

Reputation: 163

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

Upvotes: 0

chuff
chuff

Reputation: 5876

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

Upvotes: 3

Craig T
Craig T

Reputation: 2752

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.

Upvotes: 2

Related Questions