[Range].Find Help

[Dan R.]

I'm using the following to search for values on each sh, if/when found
it copies values back to the activesheet. But if the value is in
multiple columns it sometimes uses that value instead. Since it's
only supposed to search in column 3 why would it even find that value
in the first place?

With sh
xEnd = .Cells(Rows.count, 3).End(xlUp).Row
Set xRng = .Range(.Cells(2, 3), .Cells(xEnd, 3))
End With

Set rng = xRng.Find( _
What:=i.Value, _
LookIn:=xlValues, _
MatchCase:=False)

Thanks,
-- Dan

 

[Dave Peterson]

I've never seen .find work that way.

Maybe it's time to double check to see what was found:

after the "set rng = xRng.find(...)" stuff:

if rng is nothing then
msgbox "not found"
else
msgbox rng.address & vblf & xrng.address
end if

===
Maybe it's picking up a partial match that you don't expect.

I would specify all the parms for that .Find() statement. If you don't, then
excel/VBA will use what was ever last used (either in code or by the user). And
that may not be what you want.

 

[Dan R.]

I'm sure the problem lies somewhere in the rest of my code but I
figured I'd ask. Thanks again Dave.

-- Dan