Using VB to search for a value within one column

H

Hcoms

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!!!
 
D

Dave Peterson

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.)
 
D

Dick Kusleika

Hcoms
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
Click to expand...

ws.Columns(1).Find (etc...

just use the Find method on whatever range you want to search and it will
search only that range. ws.Cells means every cell on the worksheet.
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.
Click to expand...

If you're limiting your search to one column, the rows thing would work. I
usually use the Address property as described here

http://www.dicks-blog.com/excel/2004/03/the_find_method.html
 
J

JE McGimpsey

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.
 
H

Hcoms

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
 
Top