Why is my .Findnext not working?

[CompleteNewb]

I'm doing a "find", then when I find something I do a whole bunch of stuff,
then I findnext. In one area of my code, where I'm building an array and
not setting values in the sheets yet, it totally works; it loops through and
keeps going. In this section, though, I'm doing some operations and setting
some cell values. Now, when I reach the .FindNext, I get nothing:

With shtRaw.Columns("C")
Set foundit = .Find("H2H", LookIn:=xlValues, LookAt:=xlPart)

If Not foundit Is Nothing Then
FirstAddress = foundit.Address

SHtQC.Range("C2") = "H2H"

Set QCStartRange = SHtQC.Range("A3").End(xlToRight).Offset(0,
1)
Set QCStartRange = QCStartRange.Resize(UBound(ElementList) -
LBound(ElementList) + 1, 1)

Do

StartRange =
shtRaw.Range(foundit.Address).Offset(3, -2).Address
EndRange =
shtRaw.Range(StartRange).End(xlDown).Offset(0, 9).Address
Set RawRange = shtRaw.Range(StartRange, EndRange)

For Each cell In RawRange
If Not
IsError(Application.VLookup(cell.Value, QCElMass, 1, False)) Then
SHtQC.Cells(QCElMass.Find(cell.Value,
LookIn:=xlValues, LookAt:=xlPart).Row, QCStartRange.Column).Value =
Application.VLookup(cell.Value, shtRaw.Range(StartRange, EndRange), 5,
False)
End If
Next cell

Set foundit = .FindNext(foundit)
Loop While Not foundit Is Nothing And foundit.Address <>
FirstAddress
End If
End With

I have a With statement earlier that's almost EXACTLY like this, except I
just build an array before the "End With." It loops through several
instances of "H2H." This With statement, though, finds the first "H2H"
value (and it's the same 1st one my previous With staement finds, it doesn't
start lower or anything), then at the Set foundit=.FindNext line, Foundit is
set to nothing, like it doesn't see any other "H2H". The only thing I can
think of as being the issue is that, even though I'm not selecting cells, my
setting of values, etc. in other sheets is taking Excel off the range it was
looking at (shtSheet1.Columns("C"). I tried to address this by, instead of
saying With shtSheet1.Columns("C") , I made a named range that was
specifically Sheet1's Column C, and referred to that in the With. Still no
good. My Find loop above this one works, this one sets FoundIt to nothing
after it finds the first "H2H."

Can anyone help me fiugre out why this would happen?

If you want to see the one that's working fine, here it is:

With shtRaw.Columns("C")

Set foundit = .Find("H2H", LookIn:=xlValues, LookAt:=xlPart)

If Not foundit Is Nothing Then
firstaddress = foundit.Address

Do

StartRange =
shtRaw.Range(foundit.Address).Offset(3, -2).Address
EndRange = shtRaw.Range(StartRange).End(xlDown).Address

For Each cell In shtRaw.Range(StartRange, EndRange)
ReDim Preserve ElementList(0 To i)
ElementList(i) = cell.Value
i = i + 1
Next cell

Set foundit = .FindNext(foundit)

Loop While Not foundit Is Nothing And foundit.Address <>
firstaddress
i = 0
End If

End With

 

[Per Jessen]

The problem is that you have a 'extra' find statement in the For/Next
loop, which mess up the initial .Find routine

To solve it substiture Set FoundIt=FindNext(FindIt) with:

Set FoundIt = .Find("H2H", after:=FoundIt, LookIn:=xlValues,
LookAt:=xlPart)

Regards,
Per

 

[CompleteNewb]

Thanks, Per, that fixed it.

I'm curious, though, is this a flaw with Find? The first Find is in its own
With statement, plus it retains its value all the way to the Loop statement
(using Find in the interim didn't reset or lose the original Find's value),
so it seems odd that any use of Find in between would cause such an issue.

I'm just curious, because it doesn't seem to make sense to me the way other
erros in my code do. The second find, if it was interfering with the
FindNext, seems like it would find the next value of my SECOND Find, which
DOES exist, but the Foundit was set to Nothing, not to my second Found
value.

I'm looking to know what to watch out for with the Find method, as I'm just
getting into it.

Thanks again, you literally saved me hours of experimenting plus giving up
on Find; I never would have thought that was the issue.


The problem is that you have a 'extra' find statement in the For/Next
loop, which mess up the initial .Find routine

To solve it substiture Set FoundIt=FindNext(FindIt) with:

Set FoundIt = .Find("H2H", after:=FoundIt, LookIn:=xlValues,
LookAt:=xlPart)

Regards,
Per

 

[Per Jessen]

Thanks for your reply.

It is not at flaw with Find. It has nothing to do with the With
statement, you set up a Find where you search for H2H then you set up
several Find functions in the For/Next loop. So when you
use .FindNext, it will look for 'Cell.Value' after FindIt cell.

You will see the same behaviour when you use a manual Find in a
worksheet. First you search for H2H in a range, then you can use
FindNext. But if you use find to search for another value in another
range, then if you use FindNext the function will look for the second
value in the second range.

Per

 

[lukeboulder]

I tried findnext, find 2x, filling an array and outputting only what was found, etc. and NOTHING worked for me. What I finally ended up doing, and would suggest for anyone trying to solve this problem, is to find the first element, then set a range as this row as well as a range at the bottom of the Excel, and then find in this range in order to find the next element. Like this:

With shtRaw
Set foundit = .Range("C").Find("H2H", LookIn:=xlValues, LookAt:=xlPart)

Do While Not foundit Is Nothing

rngID = shtRaw.Range(foundit.Address).End(xlDown).Row
lRow = shtRaw.Cells(Rows.Count, 1).End(xlDown).Row

Set foundit = .Range("C" & rngID & ":C" & lRow).Find(What:="H2H", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext)
Loop
End With

This will work if you are doing both finds right next to each other without other finds in-between, or if you are doing other finds in-between. RECOMMENDED!