Continue Finding

aturnon · Apr 6th 2007

I have this vba code and i want it to carry on finding the next cell with the same value, so every time i click the button it'll find the next cell with the same value. E.g. i type in mark it'll find the first Mark, then click again it'll find the second Mark and so on

Code

Sub Find_cust()


      If Range("N3").Value = "" Then
      MsgBox "Please enter a customer name"
      Exit Sub
      
      End If


      valueToLookFor = Range("N3").Value
      Set found = Worksheets("CD").Range("b:b").Find(valueToLookFor, LookIn:=xlValues)
      If Not found Is Nothing Then
         iRow = found.Row
         Cells(iRow, "A").Select


End If


End Sub

Display More

cbanks · Apr 6th 2007

Re: Continue Finding

The code below will continue until N is blank. It appears as if you are starting your seach with N3. Therefore row begins with 3. If you begin with another row then you can change it.

Code

Sub Find_cust()
Dim row As String
row = 3
Do Until Worksheets(1).Range("N" & row) = ""
    If Range("N3").Value = "" Then
        MsgBox "Please enter a customer name"
        Exit Sub
         
    End If
     
    valueToLookFor = Range("N3").Value
    Set found = Worksheets("CD").Range("b:b").Find(valueToLookFor, LookIn:=xlValues)
    If Not found Is Nothing Then
        iRow = found.row
        Cells(iRow, "A").Select
         
    End If
     row = row + 1
        DoEvents
    Loop
   
End Sub

Display More

aturnon · Apr 6th 2007

Re: Continue Finding

I don't think i explained it correctly.

I type it say Mark in N3. It then looks in column B finds mark and selects the cell in column A where Mark is found in Column B.

However i want it to be able to click my button again and it'll select the next cell that has Mark in Column B. If you get what i mean

**norie** · Apr 6th 2007

Re: Continue Finding

Could you not just use a filter to get all the rows with the value in it?

aturnon · Apr 6th 2007

Re: Continue Finding

Yea i cud try that, thanks

anka1829 · Apr 6th 2007

Re: Continue Finding

Try with this code (may be it's possible to do shorter code) that select the cell with a row number greater than current position (activecell):

Code

Sub Find_cust()
   Dim iRow As Long
   Dim icurrentRow As Long
   Dim lastRow As Long
   Dim swEnd As Boolean
   icurrentRow = ActiveCell.row
   
   lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
   
   If Range("N3").Value = "" Then
      MsgBox "Please enter a customer name"
      Exit Sub
   End If
   valueToLookFor = Range("N3").Value
   
   With Worksheets("CD").Range("b:b")
      Set found = .Find(valueToLookFor, LookIn:=xlValues)
      If Not found Is Nothing Then
         iRow = found.row
         firstAddress = found.Address
      
         swEnd = False
         Do Until (Not found Is Nothing _
               And iRow > icurrentRow) _
            Or (found.Address = firstAddress _
               And swEnd) _
            Or found Is Nothing
            DoEvents
            
            swEnd = True
            Set found = .FindNext(found)
            If Not found Is Nothing Then
               iRow = found.row
            End If
         Loop
      End If
   End With
   
   If iRow > 0 Then
      Cells(iRow, "A").Select
   End If
End Sub

Display More

Regards,
Antonio

Continue Finding

Participate now!

Share