You need to change the error handling and test the result of Match to see if
it returned a valid value:

Dim V As Variant
On Error Resume Next
V = Application.WorksheetFunction.Match("cx", Range("A1:A5"), 0)
If IsEmpty(V) = True Then
Debug.Print "Match value not found"
Else
Debug.Print "Result: " & CStr(V)
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
' OR
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

Dim V As Variant
On Error Resume Next
V = Application.Match("cx", Range("A1:A5"), 0)
If IsError(V) = True Then
Debug.Print "Match value not found"
Else
Debug.Print "Result: " & CStr(V)
End If

Note that the code differs in how it tests V with regard to whether
"WorksheetFunction" is included in the call to Match.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"Li Jianyong" wrote in message
...
Dear Peterson,

thanks for your message.

my problem is on the code:

R=application.worksheetfunction.match( 19031,Trange,0)

it seems dosn't workk. the err.number 1004

Please check it for me,how to improve the code to make it works.

Best regards
Li Jianyong

"Dave Peterson" wrote:

If you use .worksheetfunction, and there is no match, you'll get a
runtime
error.

I use this instead:

Dim R as variant 'could be an error
Dim Trange as range

Set Trange=sheets("drawing").range("G2:G235")

R=application.match( 19031,Trange,0)

if iserror(r) then
msgbox "not found"
else
Msgbox R
end if

=========
You could also check for the runtime error:

Dim R as long
Dim Trange as range

Set Trange=sheets("drawing").range("G2:G235")

on error resume next
R=application.worksheetfunction.match( 19031,Trange,0)
if err.number < 0 then
msgbox "Not found"
err.clear
else
Msgbox R
end if
on error goto 0

Li Jianyong wrote:

I am trying to use worksheetfunction.match to get the data postion in a
range named sheets("drawing").range("G2:G235")

the code is

Dim R as long
Dim Trange as range

Set Trange=sheets("drawing").range("G2:G235")

R=application.worksheetfunction.match( 19031,Trange,0)

Msgbox R
....

I am sure the no. 19031 mentioned above are in the range. But when I
run
this program, it appears err.number 1004. I can not get the R value

please provide help. thanks

Li Jianyong

--

Dave Peterson