Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
I'm trying to write VBA using either Search or Find to determine if string A
exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
It would help if you provided some source code so we could see what you are
doing. I use "Find" quite a bit in my code. Are you using it as a member of a range object? Are you setting a range object to the return value? The following code works for me. It finds a cell in column 2 that contains the text "Six Sigma.": Sub TestFind() Dim r As Excel.Range Set r = Worksheets("Sheet1").Columns(2).Find("Six Sigma", , xlValues, xlPart) End Sub "LarryP" wrote in message ... I'm trying to write VBA using either Search or Find to determine if string A exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
The code is nothing fancy, just testing whether a certain filename exists
within a filepathname. (What I'm actually using it for is to confirm that a user has saved a template file under another name; if not, it throws him out.) Like this: If Find("OriginalFile.xls", "c:\Documents And Settings\Stuff\SavedAsFile.xls", 1) Then MsgBox ("Naughty, naughty, same filename -- you'e outa here!") End If Compiling or running this, though, gives a Compile error: Sub or Function not defined. Exact same if I use Search instead of Find. "SixSigmaGuy" wrote: It would help if you provided some source code so we could see what you are doing. I use "Find" quite a bit in my code. Are you using it as a member of a range object? Are you setting a range object to the return value? The following code works for me. It finds a cell in column 2 that contains the text "Six Sigma.": Sub TestFind() Dim r As Excel.Range Set r = Worksheets("Sheet1").Columns(2).Find("Six Sigma", , xlValues, xlPart) End Sub "LarryP" wrote in message ... I'm trying to write VBA using either Search or Find to determine if string A exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
On May 21, 1:03 pm, LarryP wrote:
The code is nothing fancy, just testing whether a certain filename exists within a filepathname. (What I'm actually using it for is to confirm that a user has saved a template file under another name; if not, it throws him out.) Like this: If Find("OriginalFile.xls", "c:\Documents And Settings\Stuff\SavedAsFile.xls", 1) Then MsgBox ("Naughty, naughty, same filename -- you'e outa here!") End If Compiling or running this, though, gives a Compile error: Sub or Function not defined. Exact same if I use Search instead of Find. "SixSigmaGuy" wrote: It would help if you provided some source code so we could see what you are doing. I use "Find" quite a bit in my code. Are you using it as a member of a range object? Are you setting a range object to the return value? The following code works for me. It finds a cell in column 2 that contains the text "Six Sigma.": Sub TestFind() Dim r As Excel.Range Set r = Worksheets("Sheet1").Columns(2).Find("Six Sigma", , xlValues, xlPart) End Sub "LarryP" wrote in message ... I'm trying to write VBA using either Search or Find to determine if string A exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) Hello LarryP, Here is a function that takes the full file path and returns the file name. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Function GetFileName(ByVal FullFilePath As String) As String Dim FSO As Object Dim FileName As String FilePath = "C:\Documents and Settings\Owner\My Documents\Test Document.xls" Set FSO = CreateObject("Scripting.FileSystemObject") GetFileName = FSO.GetFileName(FilePath) Set FSO = Nothing End Function ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sincerely, Leith Ross |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
Find is not a native command. It has to be used as a member of one of the
objects that supports it. I think you want the Instr function to accomplish your goal. From help: InStr Function Returns a Variant (Long) specifying the position of the first occurrence of one string within another. Syntax InStr([start, ]string1, string2[, compare]) The InStr function syntax has these arguments: Part Description start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified. string1 Required. String expression being searched. string2 Required. String expression sought. compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison. "LarryP" wrote in message ... The code is nothing fancy, just testing whether a certain filename exists within a filepathname. (What I'm actually using it for is to confirm that a user has saved a template file under another name; if not, it throws him out.) Like this: If Find("OriginalFile.xls", "c:\Documents And Settings\Stuff\SavedAsFile.xls", 1) Then MsgBox ("Naughty, naughty, same filename -- you'e outa here!") End If Compiling or running this, though, gives a Compile error: Sub or Function not defined. Exact same if I use Search instead of Find. "SixSigmaGuy" wrote: It would help if you provided some source code so we could see what you are doing. I use "Find" quite a bit in my code. Are you using it as a member of a range object? Are you setting a range object to the return value? The following code works for me. It finds a cell in column 2 that contains the text "Six Sigma.": Sub TestFind() Dim r As Excel.Range Set r = Worksheets("Sheet1").Columns(2).Find("Six Sigma", , xlValues, xlPart) End Sub "LarryP" wrote in message ... I'm trying to write VBA using either Search or Find to determine if string A exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and Find Missing In Action
OhForPeteSake! I use InStr all the time in Access VBA, but knowing how Excel
likes to call things something just a bit different, I searched help for "list of functions" and found Search and Find. Their structure looked pretty much like Instr, so I said "hey, that's gotta be what I want." Clear case of over-thinking the problem. Thanks to you, and to all who responded. "SixSigmaGuy" wrote: Find is not a native command. It has to be used as a member of one of the objects that supports it. I think you want the Instr function to accomplish your goal. From help: InStr Function Returns a Variant (Long) specifying the position of the first occurrence of one string within another. Syntax InStr([start, ]string1, string2[, compare]) The InStr function syntax has these arguments: Part Description start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified. string1 Required. String expression being searched. string2 Required. String expression sought. compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison. "LarryP" wrote in message ... The code is nothing fancy, just testing whether a certain filename exists within a filepathname. (What I'm actually using it for is to confirm that a user has saved a template file under another name; if not, it throws him out.) Like this: If Find("OriginalFile.xls", "c:\Documents And Settings\Stuff\SavedAsFile.xls", 1) Then MsgBox ("Naughty, naughty, same filename -- you'e outa here!") End If Compiling or running this, though, gives a Compile error: Sub or Function not defined. Exact same if I use Search instead of Find. "SixSigmaGuy" wrote: It would help if you provided some source code so we could see what you are doing. I use "Find" quite a bit in my code. Are you using it as a member of a range object? Are you setting a range object to the return value? The following code works for me. It finds a cell in column 2 that contains the text "Six Sigma.": Sub TestFind() Dim r As Excel.Range Set r = Worksheets("Sheet1").Columns(2).Find("Six Sigma", , xlValues, xlPart) End Sub "LarryP" wrote in message ... I'm trying to write VBA using either Search or Find to determine if string A exists in string B, and the debugger insists it doesn't recognize either, although Help alleges they are available to VBA. Anyone have an idea why the error? No missing references.... (Excel 2003 SP3/Windows XP) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search a Range take Action | Excel Programming | |||
Loop through range of cells, string search, take action | Excel Programming | |||
where do I find the the action icon in regards to a meeting? | Excel Worksheet Functions | |||
Find a word and then take certain action, else | Excel Worksheet Functions | |||
Using text boxes to find value, then do some action | Excel Programming |