CODEWITHSUNDEEP
vbaexcelloops
mond1111
mond1111

Reputation: 105

using a do loop on a range.find method keeps looping over the same cells

i hope this is something you can help me with. i am trying to use a loop to dynamically go over 2 different ranges and look for instances of repeat values.

here is my code for the loop:

'my code first looks into a range and finds if a cell already exists then assigns that cell row to RowFound (there can be multiple matches of the same value)

RowFound = Find_Row(MatchVal, ColRangeA)
MatchVal = "ABC"
Do Until RowFound = 0
    'i want to loop until all rows of 'ABC' have been found so i can do the comparison in the below if statement and apply other code once found
    If MatchVal = .Cells(RowFound, ColRangeA) or MatchVal = .Cells(RowFound, ColRangeB) then 'do something
    RowFound = Find_Row(MatchVal, ColRangeA, RowFound)
Loop

Here is the code for my Find_Row function:

Function Find_Row(VAL as String, RNGCOL as Integer, Optional ByVal VALROW as Long) as Long
    Dim ValCell as Range

    Find_Row = 0
    If VALROW = 0 Then VALROW = 1

    With Thisworkbook.Sheets(1)
        Set ValCell = .Columns(RNGCOL).Find(What:=VAL, _
                        After:=.Cells(VALROW, RNGCOL), _
                        LookAt:=xlPart)

        If Not ValCell Is Nothing Then Find_Row = ValCell.Row

    End with    
End Sub

What ends up happening here is that I keep looping over the range! So let's say my range looks like this:

  1. DDD
  2. DFG
  3. ABC
  4. EKG
  5. ABC

My loop will correctly return row 3 and then row 5. However, it will continue looping as it will just go back to 3 on the next call, etc.

What am I doing wrong? If I am using After, I would expect it to stop at the end of my range?

Upvotes: 0

Views: 192

Answers (1)

OldUgly
OldUgly

Reputation: 2119

Scott's reference is the correct one for you. In the MS documentation it says "When the search reaches the end of the specified search range, it wraps around to the beginning of the range."

Below is one method, using your code, to capture when FIND wraps. 

FirstRowFound = 0
RowFound = Find_Row(MatchVal, ColRangeA)
MatchVal = "ABC"
Do Until RowFound = 0
    'i want to loop until all rows of 'ABC' have been found so i can do the comparison in the below if statement and apply other code once found
    If MatchVal = Sheets(1).Cells(RowFound, ColRangeA) Or MatchVal = Sheets(1).Cells(RowFound, ColRangeB) Then 'do something
        Debug.Print "Found at " & RowFound
    End If
    RowFound = Find_Row(MatchVal, ColRangeA, RowFound)
    If FirstRowFound = 0 Then
        FirstRowFound = RowFound
    Else
        If RowFound = FirstRowFound Then RowFound = 0
    End If
Loop

Upvotes: 1

Related Questions