LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Do I need Findnext? Not sure how Tim[_51_] Excel Programming 0 February 5th 09 04:49 AM
Help with FindNext Method Dewey Excel Programming 1 August 8th 06 02:37 PM
Findnext Noemi Excel Discussion (Misc queries) 1 December 12th 05 11:23 AM
FindNext John Keturi Excel Programming 1 October 16th 04 01:56 PM
FindNext SJ[_6_] Excel Programming 7 May 21st 04 06:01 AM


All times are GMT +1. The time now is 10:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"