problem with worksheet search function in visual basic 3

[zaka2003]

Within an Excel spreadsheet I have a form whose code uses the worksheet SEARCH function. If the search string does not exist I get an error message.

For example:

N = Application.WorksheetFunction.Search("R", "SAMPLE")

This gives an error "Unable to get the serach property of the worksheet function class".

But it works as:
N = Application.WorksheetFunction.Search("S", "SAMPLE")

Is there a way to surpass the error message? I tried trhe ISERR function but it did not work.

Any help much appreciated

Thanks

 

[SkipVought]

Hi,

That's not the statement that you are getting the error on.

Dim N
...
N = Application.WorksheetFunction.Search("S", "SAMPLE")
if iserror(N) then
  'youy code here
end if

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[zaka2003]

Thanks for that but the declaration is already there as
dim n integer.
Using
N = Application.WorksheetFunction.Search("S", "SAMPLE")
works because "S" exists within "SAMPLE". If you try "R" for example, then
N = Application.WorksheetFunction.Search("R", "SAMPLE") still gives an error!

 

[xlbo]

how about not using the worksheetfunction then and use the one that is native to VBA - INSTR()

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

You CANNOT define N as INTEGER

Dim N

as variant by default.

That's why I included Dim N in the example.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[zaka2003]

thanks Geoff! that function solves the problem but there must be some way to resolve the SEARCH function issue.
Rgds

Cherif

 

[SkipVought]

Cherif... "...but there must be some way to resolve the SEARCH function issue."

Hellooo???

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[zaka2003]

Skip, the issue remains unresolved! As I said, declaring dim n (or adding a format to n) makes no difference. Did you try it with a substring that's not in the search string?

For example:

N = Application.WorksheetFunction.Search("R", "SAMPLE").

rgds

Cherif

 

[SkipVought]

    Dim N
    On Error Resume Next
    N = Application.WorksheetFunction.Search("R", "SAMPLE")
    If IsEmpty(N) Then
        MsgBox "Not Found"
    End If

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[cheerio]

If you have lots of searches create your own custom function

Public Function NewSearch(strSearch As String, strTarget As String) As Integer
Dim N As Integer

On Error Resume Next
N = Application.WorksheetFunction.Search(strSearch, strTarget)
If Err.Number <> 0 Then
   NewSearch = 0
Else
   NewSearch = N
End If
End Function

 

[zaka2003]

Skip, Now you're talking! Many thanks for your help!
It's that "On Error Resume Next" that does it.

And thanks to Cheerio for the generalisation of the method by creating user functions.

Cheers

Cherif