Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Do I need Findnext? Not sure how | Excel Programming | |||
Help with FindNext Method | Excel Programming | |||
Findnext | Excel Discussion (Misc queries) | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming |