Excel2000 VBA: A problem with worksheetfunction MATCH

[Arvi Laanemets]

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!

 

[Dave Peterson]

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.

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]

Hi

I did get a solution from german NG meanwhile - in Match() I had to convert
the date, I was searching for, to long integer.
......
varRow1 = Application.WorksheetFunction.Match(CLng(varDate),
[DataDate], 0) + 1

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

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.

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!

 

[Dave Peterson]

If that date isn't found, you may still have a problem.

I still like this:

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

And if you're going to post the same message to multiple newsgroups, please post
once and include all the newsgroups in your header. Crossposting is fine.
Multiposting means that one of us wasted his time.

Thanks,

Hi

I did get a solution from german NG meanwhile - in Match() I had to convert
the date, I was searching for, to long integer.
.....
varRow1 = Application.WorksheetFunction.Match(CLng(varDate),
[DataDate], 0) + 1

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

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.

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]

Hi

If that date isn't found, you may still have a problem.

No problem in my procedure (at least this code row). This row is from delete
routine which is a part of procedure. User is asked for source workbook, and
then for date. When returned date is found in table, user is asked, want
he/she overwrite the data. When yes, then the first (varRow1) and last
(varRow2) row with this date are found (data are always sorted by date), and
the range between is deleted. After that new data are read into table and
table is sorted.

And if you're going to post the same message to multiple newsgroups, please post
once and include all the newsgroups in your header. Crossposting is fine.
Multiposting means that one of us wasted his time.

No multiposting of course. In other NG the posting was in german.


Arvi Laanemets

 

[Dave Peterson]

Excellent point!

I'm not sure what the newsgroup etiquette is for different languages--maybe one
post with the question in German followed by the translated question???

But my point about using application.worksheetfunction raising an error still
needs addressing (I think).

I didn't see anything in your original short snippet that would show that you're
looking for that kind of error.

 

[Arvi Laanemets]

Excellent point!

I'm not sure what the newsgroup etiquette is for different
languages--maybe one
post with the question in German followed by the translated question???

But my point about using application.worksheetfunction raising an error
still
needs addressing (I think).

Look at my another posting from yesterday (Excel2000 VBA: How force the
procedure to wait until queries are refreshed?). It's not the same procedure
I was speaking about here, but another one from same application. It
contains almost same code for deleting of rows. As you can see, the code row
with application.worksheetfunction is processed only, when varDate is found
in range DataDate.

 

[Dave Peterson]

I didn't look at the other post and I didn't notice a connection.