Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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 wrote in message ps.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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 " 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Error, Returns 0
On Nov 5, 8:55 am, Tom Ogilvy
wrote: 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 " 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 - Ty Im gonna give this a try and hope it works :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF(AND(OR function returns #VALUE! error | Excel Worksheet Functions | |||
LINEST() function returns error. | Excel Discussion (Misc queries) | |||
Find function returns the #VALUE! error value | Excel Discussion (Misc queries) | |||
sumproduct function returns #value or #ref error | Excel Worksheet Functions | |||
Function returns Range -- error 91 | Excel Programming |