dim FoundCell as range
with ws.range("a:a")
set foundcell = .cells.find(....,after:=.cells(.cells.count), _
searchdirection:=xlnext, ...)
end with

And look in VBA's help for .find. You'll find a nice example that uses the
address of the first find and uses that to determine when it's starting over.

(Row number would work if you were working with a single column, too.)



Hcoms wrote:

Hello,

How can you search for a value in a sheet while only looking in one column?
I use the below code to use the find feature but it searches all cols.

ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

I wish to search and then display all sheets that have a value in it ( i.e.
test) I have done the code for looping through a number of workbooks and
then all the worksheets . However what is the best way to check for a value
in a sheet. Is it the code above? And if so how do you prevent the search
finding the values more than once? I presently compare row numbers , i.e. if
the last row where the value was found is greater or equal to the new found
value then it must be starting at the top of the page again.

Any help would be appreicated!!!

--

Dave Peterson