Function Error, Returns 0

[gwoodby]

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

 

[Zone]

Would probably be better to give it a slighly different name, since SEARCH
is the name of a built-in Excel function. However, it works fine for me if
I type mary into a cell and call the function like this:

Sub Test()
MsgBox Search("mary")
End Sub

How are you calling the function?
James

 

[Don Guillett]

If you are trying to get a count of the text, try this idea

Sub adduptest()
mywhat="xx"
For Each ws In Worksheets
With ws.Cells
Set c = .Find(mywhat, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = mc + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next ws
MsgBox mc
End Sub

 

[Tom Ogilvy]

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

 

[gwoodby]

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








- 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

 

[Tom Ogilvy]

Private Sub CmdSelect_Click()
Dim IntSearch As Integer
IntSearch = MYSearch(Me.LBOXSELECT.Text)
Debug.Print IntSearch
If IntSearch = 1 Then
MsgBox Me.LBOXSELECT.Text

ElseIf IntSearch = 0 Then
MsgBox " No Name Found", vbOKOnly
End If
Exit Sub
ErrorHandler:
MsgBox " Error"
End Sub

Worked for me.

Target was on a separate sheet.

 

[gwoodby]

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?

 

[Tom Ogilvy]

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.

 

[gwoodby]

Private Sub CmdSelect_Click()
Dim IntSearch As Integer
IntSearch = MYSearch(Me.LBOXSELECT.Text)
Debug.Print IntSearch
If IntSearch = 1 Then
MsgBox Me.LBOXSELECT.Text

ElseIf IntSearch = 0 Then
MsgBox " No Name Found", vbOKOnly
End If
Exit Sub
ErrorHandler:
MsgBox " Error"
End Sub

Worked for me.

Target was on a separate sheet.

--
Regards,
Tom Ogilvy







- Show quoted text -

Ty Im gonna give this a try and hope it works