worksheetfunction.match

L

Li Jianyong

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
 
F

franciz

Try using

Application.WorksheetFunction _
.Match(19031, Worksheets("drawing").Range("Trange"), 0)

If you use a worksheet function that requires a range reference as an
argument, you must specify a Range object.

regards,
 
D

Dave Peterson

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
 
L

Li Jianyong

Dear Franciz,

thanks for your help. But it still doesn't work. it shows same err.number
1004

Best regards
Li Jianyong
 
L

Li Jianyong

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
 
M

Mike Fogleman

Test your original data to see if it actually is a number or numbers
formatted as text. If the numbers are text, then finding Match to a number
will fail. A quick test is to Sum the numbers. If the Sum = 0 then they are
text. A quick fix is to multiply the numbers by 1. Put a 1 in an empty cell
somewhere. Right-click the cell and select Copy. Now select all your numbers
on the sheet and Paste Special/Multiply. Now clear the cell with the 1 in it
and run your Match again.
See if that will help
Mike F
 
C

Chip Pearson

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)
 
D

Dave Peterson

Did you even try either of my suggestions?

Li said:
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
 
F

franciz

Hi Yong

It works when I tested it. Try also to define G2:G235 in a Name range by
going into Insert on the Excel menu>>Name>> Define >> Trange>> select
G2:G235 and run the code again.

regards, xlsops
 
T

teri moats

application.worksheetfunction.match returns a double.
You set your variable as a long. Try setting it as a double.



Li Jianyon wrote:

worksheetfunction.match
14-Sep-08

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

the code i

Dim R as lon
Dim Trange as rang

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

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

Msgbox
....


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 valu

please provide help. thank

Li Jianyong

Previous Posts In This Thread:

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

the code i

Dim R as lon
Dim Trange as rang

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

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

Msgbox
....


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 valu

please provide help. thank

Li Jianyong

Try using Application.WorksheetFunction _ .
Try using

Application.WorksheetFunction
.Match(19031, Worksheets("drawing").Range("Trange"), 0

If you use a worksheet function that requires a range reference as an
argument, you must specify a Range object.

regards

:

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

I use this instead

Dim R as variant 'could be an erro
Dim Trange as rang

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

R=application.match( 19031,Trange,0

if iserror(r) the
msgbox "not found
els
Msgbox
end i

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

Dim R as lon
Dim Trange as rang

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

on error resume nex
R=application.worksheetfunction.match( 19031,Trange,0
if err.number <> 0 the
msgbox "Not found
err.clea
els
Msgbox
end i
on error goto

Li Jianyong wrote

--

Dave Peterson

Dear Franciz,thanks for your help. But it still doesn't work.
Dear Franciz

thanks for your help. But it still does not work. it shows same err.numbe
100

Best regard
Li Jianyon

:

Re: worksheetfunction.match
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 100

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

Best regard
Li Jianyon

:

Test your original data to see if it actually is a number or numbers formatted
Test your original data to see if it actually is a number or numbers
formatted as text. If the numbers are text, then finding Match to a number
will fail. A quick test is to Sum the numbers. If the Sum = 0 then they are
text. A quick fix is to multiply the numbers by 1. Put a 1 in an empty cell
somewhere. Right-click the cell and select Copy. Now select all your numbers
on the sheet and Paste Special/Multiply. Now clear the cell with the 1 in it
and run your Match again
See if that will hel
Mike

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

Dim V As Varian
On Error Resume Nex
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)






Re: worksheetfunction.match
Did you even try either of my suggestions?

Li Jianyong wrote:

--

Dave Peterson

Hi YongIt works when I tested it.
Hi Yong

It works when I tested it. Try also to define G2:G235 in a Name range by
going into Insert on the Excel menu>>Name>> Define >> Trange>> select
G2:G235 and run the code again.

regards, xlsops

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorial...d8-3e2773fa29b5/whats-new-for-developers.aspx
 
D

Dave Peterson

=match() returns an error if there is no match or a whole number if there is a
match.

But
r = application.worksheetfunction.match(...)
will cause a runtime error if there isn't a match.

I'd use:

Dim r as Variant 'could be an error
....set up code here
r = application.match(...)

if iserror(r) then
'not found
else
'found as the rth element.
end if
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top