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