If you use .worksheetfunction, you're going to have to trap the error yourself.

dim VarRow1 as long
on error resume next
varrow1 = application.worksheetfunction.match(...)
if err.number < 0 then
'not found
err.clear
end if
on error goto 0

If you drop the .worksheetfunction, you can test for a returned error.

dim VarRow1 as Variant 'could contain an error.
varrow1 = application.match(...)
if iserror(varrow1) then
'error was found
else
'no error
end if

=========
But working with dates can be a problem.

You could try something like this:

dim VarRow1 as variant
varRow1 = Application.Match(clng(CDate(varDate)), _
ThisWorkbook.Sheets("Data").Range("DataDate"), 0)

if iserror(varrow1) then
msgbox "no match!
else
varrow1 = varrow1 + 1
end if

Sometimes treating the dates like just plain old numbers works nicely.




Arvi Laanemets wrote:

Hi

I need to find the first occurrence of a date in range on sheet in my
procedure code. The range is on active sheet ("Data") of active workbook,
and is defined as a dynamic range DataDate (which returns a range
Data!$B$2:$B#). (Searched data is read from InputBox.) The code below
returns an error: "Unable to get the Match property of the WorksheetFunction
class"
.....
varRow1 = Application.WorksheetFunction.Match(CDate(varDate) ,
[DataDate], 0) + 1

.....

The same error is returned, when I modify the code to
.....
varDataDate = [DataDate]
.....
varRow1 = Application.WorksheetFunction.Match(CDate(varDate) ,
varDataDate, 0) + 1

.....

Both [DataDate] and varDataDate are functional - in watch window:
varDataDate - Type = Variant/Variant(1 to 4688, 1 to 1)
[DataDate] - Type = Variant/Object/Range

And the same error is returned with code:
.....
varRow1 = Application.WorksheetFunction.Match(CDate(varDate) ,
ThisWorkbook.Sheets("Data").Range("B2:B" & ([DataRows]-1)), 0) + 1
.....

where the named range DataRows returns last used row on sheet Data.

I'm trying to solve this from yesterday on - and no success so long. Maybe
someone here can help!

Thanks in advance!

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

--

Dave Peterson