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.



"Dave Peterson" wrote:

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

Dave Peterson wrote:

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.

Paul D Byrne wrote:

Thanks Dave - that's right, however with the other example if you do
?IsError(Application.WorksheetFunction.Search("*Va lue*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 Byrne

"Dave Peterson" wrote:

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

?IsError(Application.WorksheetFunction.Search("*Va lue*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.



Paul D Byrne wrote:

Hi,

I am trying to test a string in VBA to see if it exists using the following :
Iserror(Application.WorksheetFunction.Search("*Val ue*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("*Val ue*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


--

Dave Peterson

--

Dave Peterson