Iserror with Search

[Paul D Byrne]

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.

 

[Dave Peterson]

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.

 

[Joel]

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.

 

[Paul D Byrne]

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.

 

[Paul D Byrne]

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.

 

[Joel]

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",

 

[Dave Peterson]

The =search() worksheet function returns an error.

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

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>

 

[Paul D Byrne.]

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:

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>