Find in VBA - unexpected behaviour

[jaerha]

Sorry! Tried to install the XL2BB addin but did not succeed after trying most of your recommendations.

Here is my question:
Running the macro Nxt standing in cell A1, with SearchValue C1 = "Coll", the SearchRange will become A1:A4 and the first match is found in A2.
It looks like Find does not start looking in A1.
However, running the macro standing in A4, the SearchRange becomes A4:1048576 and Find inconsequently finds a match in A4. How is it meant to work?

Collet	Anne	Coll
Collet	Chris
Collet	Mike
Collet	Pamela

Sub Nxt()
Dim Found As Range, SearchRange As Range
Dim SearchValue As String, AdresseFound As String, SearchRangeValue As String

'looking in column A for the content of cell C1
SearchValue = Range("C1")
Set SearchRange = Range(ActiveCell.Offset(0, 0), ActiveCell.End(xlDown))
SearchRangeValue = SearchRange.Address
Set Found = SearchRange.Cells.Find(what:=SearchValue, LookAt:=xlPart)
' AdresseFunnet = Funnet.Address
If Found Is Nothing Then
AdresseFound = SearchValue & " does not exist in " & SearchRangeValue
' Call FindFirstBlankCell
Else
AdresseFound = Found.Address
Application.Goto Reference:=Found, Scroll:=True
End If
End Sub

 

[MARK858]

Find as per it's specs starts After the cell first specified in the After Parameter, to include the first cell you set the After parameter to the last cell in the range

Range.Find method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[jolivanes]

It is a good idea to have Row1 as a header Row.

 

[jaerha]

Thanks for quick response!

Standing in the cell with the last match in the column, I expected Find to return Nothing next time the macro was run. That's however not what happens. How get to know when the last match has been found?

 

[MARK858]

You would test the cells found against the address of the first cell found then exit the sub, something like

Sub jaerha()

    Dim myCell As Range, firstaddress As String
    Application.ScreenUpdating = False
 
    With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Set myCell = .Find(Range("C1").Value, After:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlPart)
   
        If Not myCell Is Nothing Then
            firstaddress = myCell.Address
            Do
                Debug.Print myCell.Address ' WHAT YOU WANT TO HAPPEN GOES HERE
                Set myCell = .FindNext(myCell)
            Loop While Not myCell.Address = firstaddress
        End If
 
    End With
End Sub

 

[MARK858]

Edited previous post to xlPart

 

[jaerha]

Thanks a lot for your proposed solution, Mark858! It's always a lot to learn from code examples from expert programmers. I'm surely not one of them in this respect. I should at least have checked out how Find is supposed to work before posting. To avoid the wrapping in Range.Find I ended up using COUNTIF to control when there are no more occurences left. I use FIND on the range starting on the active cell when I enter the macro, and COUNTIF on the range starting after the active cell. Perhaps not optimal or elegant, but it does the job.

 

[MARK858]

Just for explanation of the code I posted

In the code I posted (adjusted for your activecell range)...

The bit in red finds the last cell in your range, so (as Find loops) the search starts from / includes the first cell in the range.
The bit in purple loops through the range and tests if the cell found is the same as the first cell found, if it is then it ends the loop.

It does also use the FindNext Method which is straightforward, but just in case the link below is to the MS page on the method.

Range.FindNext method (Excel)

Office VBA reference topic

learn.microsoft.com

Sub jaerha()

    Dim myCell As Range, firstaddress As String
    Application.ScreenUpdating = False
 
    With Range(ActiveCell, ActiveCell.End(xlDown))
        Set myCell = .Find(Range("C1").Value, After:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlPart)
 
        If Not myCell Is Nothing Then
            firstaddress = myCell.Address
            Do
                Debug.Print myCell.Address ' WHAT YOU WANT TO HAPPEN GOES HERE
                Set myCell = .FindNext(myCell)
            Loop While Not myCell.Address = firstaddress
        End If
 
    End With
End Sub

Obviously go with whatever works for you as that is the most important thing and BTW welcome to the Forum