Why is my .Findnext not working?
 

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

Why is my .Findnext not working?
 

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




On 18 Okt., 02:36, "CompleteNewb" wrote:
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

Why is my .Findnext not working?
 

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.


"Per Jessen" wrote in message
...
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




On 18 Okt., 02:36, "CompleteNewb" wrote:
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

Why is my .Findnext not working?
 

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

On 18 Okt., 13:01, "CompleteNewb" wrote:
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.

"Per Jessen" wrote in message

...
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

On 18 Okt., 02:36, "CompleteNewb" wrote:



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -