Search and Find Missing In Action

[LarryP]

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)

 

[SixSigmaGuy]

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]

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.

 

[Leith Ross]

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.

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

 

[SixSigmaGuy]

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]

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.

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.

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.