Your telling it to do a case sensitive search, looking at an part of the
formula of each that contains the word. Is that what you want?

for example:
If a cell contains a constant, it is irrelevant whether it looks at the
formula or the value, but if it contains a formula, then would the formula
contain the searched for string or does the formula results (value) contain
the searched for string.
--
Regards,
Tom Ogilvy


" wrote:

On Nov 5, 8:32 am, wrote:
On Nov 5, 8:27 am, Tom Ogilvy
wrote:





From the immediate window:
? mysearch("house")
1

Function MYSearch(StrToSearch As String)
Dim sh As Worksheet
Dim SearchTxt As String
Dim rng As Range
Dim firstAddress As String
Dim IntNumber As Integer

IntNumber = 0
SearchTxt = StrToSearch

For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=SearchTxt, _
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rng Is Nothing Then
MYSearch = IntNumber + 1
Exit For
End If
Next sh
End Function

worked for me as you can see

--
regards,
Tom Ogilvy

" wrote:
This Function Is supposed to Search each sheet in the workbook, and
return 1 if it found a match and 0 if it doesnt, Can anyone help im
really stumped :|

Function Search(StrToSearch As String)
Dim sh As Worksheet
Dim SearchTxt As String
Dim rng As Range
Dim firstAddress As String
Dim IntNumber As Integer
IntNumber = 0
SearchTxt = StrToSearch

For Each sh In ThisWorkbook.Worksheets
sh.Activate
Set rng = sh.Cells.Find(What:=SearchTxt,
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=True)
If Not rng Is Nothing Then
Search = IntNumber + 1
Exit For
End If
Next sh

End Function- Hide quoted text -

- Show quoted text -

It work on the first windown but if you put it on the second excel
sheet and start the macro with excel sheet 1 active, it wont search
through to sheet 2 :| at least not for me and if i do it on sheet
three its not working either, :|
Private Sub CmdSelect_Click()
Dim IntSearch As Integer
IntSearch = Search(LboxSelect.Text) ' Call Function to Search
If IntSearch = True Then
Found (LboxSelect.Text)
Else
GoTo ErrorHandler
End If
If IntSearch = 0 Then
MsgBox " No Name Found", vbOKOnly
End If

ErrorHandler:
MsgBox " Error"
End Sub- Hide quoted text -

- Show quoted text -

Not sure as to why its doing this but i have to select the name from a
listbox, Then the text inside the listbox that is selected should be
the text its searching for, but it returns 0 and sends me the error
message, I dont see why its doing this?