Iserror with Search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul D Byrne - 01 May 2008 01:34 GMT

Hi,

I am trying to test a string in VBA to see if it exists using the following :
Iserror(Application.WorksheetFunction.Search("*Value*Date*", "System Date",
1))

If I use the msgbox in the immediate pane to see the result I get
Run-time error '1004':
Unable to get the Search property of the WorksheetFunction class

However using the following:
Iserror(Application.WorksheetFunction.Search("*Value*Date*", "Value Date", 1))

evaluates to 'False' - which is what I expect.

Howcome the first statement does not evaluate to True?

thanks,

Paul B.
PS : The 2nd parameter will be a variable in production - using the words is
only for development.

Reply to this Message

Dave Peterson - 01 May 2008 01:52 GMT

I see False when I do this in the immediate window.

?IsError(Application.WorksheetFunction.Search("*Value*Date*", "Value Date", 1))
False

And this returned False:
MsgBox IsError(Application.WorksheetFunction _
.Search("*Value*Date*", "Value Date", 1))

But VBA has its own version of application.search. Look at InStr() in VBA's
help.

> Hi,
>
[quoted text clipped - 18 lines]
> PS : The 2nd parameter will be a variable in production - using the words is
> only for development.

Signature

Dave Peterson

Reply to this Message

Joel - 01 May 2008 06:48 GMT

The Wild Card * is confusing Excel. The code is looking at two strings
"Value Date" and "System Date". the code below will always return a false
because the string "Value Date" cannot be found inside of "System Date". I
think you want variable in the string or you want the following

Iserror(Application.WorksheetFunction.Search(Range("Value Date"),
Range("System Date"), 1))

The code abovewill look at the named range in your worksheet.

> I see False when I do this in the immediate window.
>
[quoted text clipped - 30 lines]
> > PS : The 2nd parameter will be a variable in production - using the words is
> > only for development.

Reply to this Message

Paul D Byrne - 02 May 2008 05:38 GMT

Hi Joel, thanks for taking the time to reply.

The issue is why the IsError isn't trapping the returned error value and
displaying True. It's not a named range I'm using, in the example I just want
the Search function to fail and the IsError to return True. When i can get
this working I will search a string variable instead of "System Date".

try the following :
?IsError(Application.WorksheetFunction.Search("*Value*Date*", "Value Date",
1))
displays False in the immediate window
?IsError(Application.WorksheetFunction.Search("Value Date", "System Date", 1))
gives a Run-Time error 1004 when i am expecting True.

- I have taken out the wildcards and still get the same result.
cheers,

Paul B.

Signature

Paul Byrne

> The Wild Card * is confusing Excel. The code is looking at two strings
> "Value Date" and "System Date". the code below will always return a false
[quoted text clipped - 40 lines]
> > > PS : The 2nd parameter will be a variable in production - using the words is
> > > only for development.

Reply to this Message

Paul D Byrne - 02 May 2008 05:33 GMT

Thanks Dave - that's right, however with the other example if you do
?IsError(Application.WorksheetFunction.Search("*Value*Date*", "System Date",
1))

you don't get True, but a Run-Time Error 1004, the question is, why doesn't
the IsError function trap the Run-Time Error 1004 and return True (it is an
error after all!).

cheers.

Paul.

Signature

Paul Byrne

> I see False when I do this in the immediate window.
>
[quoted text clipped - 30 lines]
> > PS : The 2nd parameter will be a variable in production - using the words is
> > only for development.

Reply to this Message

Joel - 02 May 2008 05:59 GMT

I don't think Search will ever Error. Do a test and check if Search returns
zero which indicated the string is not found. If the string is found the
return value is a number which indicates the character position of where the
1st character of the string is found

if (Application.WorksheetFunction.Search("*Value*Date*", "System Date",

> 1) = 0)

> Thanks Dave - that's right, however with the other example if you do
> ?IsError(Application.WorksheetFunction.Search("*Value*Date*", "System Date",
[quoted text clipped - 42 lines]
> > > PS : The 2nd parameter will be a variable in production - using the words is
> > > only for development.

Reply to this Message

Dave Peterson - 02 May 2008 11:45 GMT

The =search() worksheet function returns an error.

It sounds like you're confusing it with VBAs InStr.

> I don't think Search will ever Error. Do a test and check if Search returns
> zero which indicated the string is not found. If the string is found the
[quoted text clipped - 57 lines]
> > >
> > > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 02 May 2008 11:50 GMT

There are some differences in the way excel's VBA handles .worksheetfunction.

For instance, application.vlookup() and application.match() can be checked with
iserror.

But application.worksheetfunction.vlookup() and
application.worksheetfunction.match() both cause runtime errors.

The same is true with the differences between application.search() and
application.worksheetfunction.search().

Do I know why? Nope.

Does it matter which syntax you use? Yep.

But outside of the academic reasons for using
application.search/application.worksheetfunction.search, I still think that
InStr is a better solution here.

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 02 May 2008 11:57 GMT

Just to add, you could use this kind of thing:

on error resume next
msgbox application.worksheetfunction.search(...)
if err.number <> 0 then
'not found
err.clear
else
'found
end if
on error goto 0

But using iserror(application.search(...)) is easier.

And VBA's InStr is even easier (have I said that enough????) <vbg>

> There are some differences in the way excel's VBA handles .worksheetfunction.
>
[quoted text clipped - 72 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Paul D Byrne. - 12 May 2008 06:55 GMT

Hi,

this is what I ended up using :

If InStr(1, acell.Cells(1, j + 1).Value, "Value", 1) > 0 And InStr(1,
acell.Cells(1, j + 1).Value, "Date", 1) > 0 Then

intDateCol = acell(0, j + 1).Column

ElseIf InStr(1, acell.Cells(1, j + 1).Value, "End", 1) > 0 And InStr(1,
acell.Cells(1, j + 1).Value, "Date", 1) > 0 Then

intDateCol = acell(0, j + 1).Column

End If

thanks for the help guys.

Paul B.

> Just to add, you could use this kind of thing:
>
[quoted text clipped - 88 lines]
> >
> > Dave Peterson

Reply to this Message