CODEWITHSUNDEEP
excelvbafind
Luuklag
Luuklag

Reputation: 3914

.Findnext not respecting the range set

In my code I use two instances of .Find, combined with a .FindNext. Which is notoriously prone to errors, unfortunately I'm no exception. However this is the best I could come up with. Code below, where I stripped most irrelevant stuff.

The problem is that there are duplicate values, which I want to keep both, so I decided to use .Findnext if there is a duplicate, using: 

If newqst = refqst Then
    Set newqstadrs = Findrange.FindNext(after:=lstqstadrs)
Else

The problem here is that .FindNext doesn't respect that it should continue on the Findrange.Find, but continues on the FindRangeTwo.Find used here:

newrowtwo = FindRangeTwo.Find(rCell.Value, LookIn:=xlValues, lookat:=xlWhole).row

Full Code:

For o = 72 To lastrow 

    Dim refqst As String
    refqst = wss.Cells(o, 1).Value
    If Not refqst = "" Then
        If InStr(refqst, ".") > 0 Then
            Dim Findrange As Range
            Dim newqst As String
            Dim newqstadrs As Range
            Dim lstqstadrs As Range

            If newqst = refqst Then
                Set newqstadrs = Findrange.FindNext(after:=lstqstadrs)
            Else

                Select Case Left(refqst, 1)
                    Case 1
                        Set Findrange = wsa.Range(wsa.Cells(4, gewaskolom), wsa.Cells(11, gewaskolom))
                    'some more cases here
                End Select
                Set newqstadrs = Findrange.Find(refqst, LookIn:=xlValues, lookat:=xlWhole)
            End If

            If newqstadrs Is Nothing Then
            Else
                newqst = newqstadrs.Value
                Dim newrow As Long
                newrow = Findrange.Find(refqst, LookIn:=xlValues, lookat:=xlWhole).row
                Dim lstqst As String

                If Not wsa.Cells(newrow, 1) = "" Then
                    'do some stuff         
                    lstqst = refqst
                    Set lstqstadrs = newqstadrs

                ElseIf Not wsa.Cells(newrow, 2) = "" Then

                    Dim FindRangeTwo As Range
                    Set FindRangeTwo = wsa.Range(wsa.Cells(newrow, gewaskolom), wsa.Cells(wsa.Range("B" & newrow).End(xlDown).row, gewaskolom))
                    Dim SearchRange As Range
                    Set SearchRange = wss.Range(wss.Cells(o + 1, 1), wss.Cells(wss.Range("B" & o).End(xlDown).row, 1))
                    Dim rCell As Range
                    For Each rCell In SearchRange
                        Dim newrowtwo As Long
                        newrowtwo = FindRangeTwo.Find(rCell.Value, LookIn:=xlValues, lookat:=xlWhole).row
                        'do some more stuff
                    Next rCell
                    lstqst = refqst
                    Set lstqstadrs = newqstadrs
                End If
            End If            

        End If
    End If
Next o

Upvotes: 1

Views: 31

Answers (1)

user4039065
user4039065

Reputation:

You can only have one Find/FindNext pair. The second overrides the first. You need an alternate method for FindRangeTwo. Given that FindRangeTwo is a single column (gewaskolom) and you are looking for the row, application.match should do nicely.

Something like this,

dim newrowtwo as variant   '<~~ should be variant type for IsError to catch

...
newrowtwo = application.match(rCell.Value, FindRangeTwo, 0)
if not iserror(newrowtwo) then
    ...
end if
...

Note that application.match is returning the position within FindRangeTwo, not the row on the worksheet. The actual row on the worksheet would be (newrowtwo + newrow - 1).

Upvotes: 2

Related Questions