Thank you all for your help! and to Dick for once again helping me out. I am
going to check your website in future first before i ask a question! The
only problem i now have is how to close excel. Once my code is finished i
close Excel ( or try to!!!) with this code. ( i use late binding by the way)
xlapp.Quit
xlapp.UserControl = False

Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

Then when you try running the code ( without closing the vb project) again
i either get a type mismatch on the find code or if you try looking at a
specific cell or selecting it ( cells(1,1).select ) it errors with
1004 , Method "cells of object" _Global failed.

I presume this is something to do with excel not closing completely. Because
when you then close the vb program and start it again it is fine! Any
Ideas??

Cheers

"JE McGimpsey" wrote in message
...
One way:

Public Sub Test()
Const ExcelSearch As String = "test"
Dim wsSheet As Worksheet
Dim rFound As Range
For Each wsSheet In Worksheets
Set rFound = wsSheet.Columns(1).Find( _
What:=ExcelSearch, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
Application.GoTo rFound
MsgBox "Found " & ExcelSearch & " on sheet " & _
wsSheet.Name
End If
Next wsSheet
End Sub


This will only display one msgbox for each sheet that contains "test" in
column A. There's no need to compare rows - just move on to the next
sheet.

In article , "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!!!