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

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,


"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

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

Dear Franciz,

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

Best regards
Li Jianyong

"franciz" wrote:

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,


"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

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

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
"Li Jianyong" <Li wrote in message
...

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

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

Did you even try either of my suggestions?

Li Jianyong wrote:

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


--

Dave Peterson

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 menuName Define Trange select
G2:G235 and run the code again.

regards, xlsops

"Li Jianyong" wrote:

Dear Franciz,

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

Best regards
Li Jianyong

"franciz" wrote:

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,


"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

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

Previous Posts In This Thread:

On Sunday, September 14, 2008 4:39 AM
Li Jianyon wrote:

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

On Sunday, September 14, 2008 5:24 AM
franci wrote:

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,


"Li Jianyong" wrote:

On Sunday, September 14, 2008 8:20 AM
Dave Peterson wrote:

If you use .
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:

--

Dave Peterson

On Sunday, September 14, 2008 8:33 AM
LiJianyon wrote:

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.number
1004

Best regards
Li Jianyong

"franciz" wrote:

On Sunday, September 14, 2008 8:38 AM
LiJianyon wrote:

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 1004

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

Best regards
Li Jianyong

"Dave Peterson" wrote:

On Sunday, September 14, 2008 8:54 AM
Mike Fogleman wrote:

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 help
Mike F
"Li Jianyong" <Li wrote in message
...

On Sunday, September 14, 2008 8:56 AM
Chip Pearson wrote:

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 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
...

On Sunday, September 14, 2008 11:18 AM
Dave Peterson wrote:

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

Li Jianyong wrote:

--

Dave Peterson

On Sunday, September 14, 2008 11:26 AM
franci wrote:

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 menuName Define Trange select
G2:G235 and run the code again.

regards, xlsops

"Li Jianyong" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorials...evelopers.aspx

Tested OK. Try using vba FIND instead

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teri moats" wrote in message ...
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 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

Previous Posts In This Thread:

On Sunday, September 14, 2008 4:39 AM
Li Jianyon wrote:

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

On Sunday, September 14, 2008 5:24 AM
franci wrote:

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,


"Li Jianyong" wrote:

On Sunday, September 14, 2008 8:20 AM
Dave Peterson wrote:

If you use .
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:

--

Dave Peterson

On Sunday, September 14, 2008 8:33 AM
LiJianyon wrote:

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.number
1004

Best regards
Li Jianyong

"franciz" wrote:

On Sunday, September 14, 2008 8:38 AM
LiJianyon wrote:

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 1004

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

Best regards
Li Jianyong

"Dave Peterson" wrote:

On Sunday, September 14, 2008 8:54 AM
Mike Fogleman wrote:

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 help
Mike F
"Li Jianyong" <Li wrote in message
...

On Sunday, September 14, 2008 8:56 AM
Chip Pearson wrote:

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 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
...

On Sunday, September 14, 2008 11:18 AM
Dave Peterson wrote:

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

Li Jianyong wrote:

--

Dave Peterson

On Sunday, September 14, 2008 11:26 AM
franci wrote:

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 menuName Define Trange select
G2:G235 and run the code again.

regards, xlsops

"Li Jianyong" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorials...evelopers.aspx

=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



teri, moats wrote:

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

Previous Posts In This Thread:

On Sunday, September 14, 2008 4:39 AM
Li Jianyon wrote:

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

On Sunday, September 14, 2008 5:24 AM
franci wrote:

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,

"Li Jianyong" wrote:

On Sunday, September 14, 2008 8:20 AM
Dave Peterson wrote:

If you use .
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:

--

Dave Peterson

On Sunday, September 14, 2008 8:33 AM
LiJianyon wrote:

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.number
1004

Best regards
Li Jianyong

"franciz" wrote:

On Sunday, September 14, 2008 8:38 AM
LiJianyon wrote:

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 1004

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

Best regards
Li Jianyong

"Dave Peterson" wrote:

On Sunday, September 14, 2008 8:54 AM
Mike Fogleman wrote:

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 help
Mike F
"Li Jianyong" <Li wrote in message
...

On Sunday, September 14, 2008 8:56 AM
Chip Pearson wrote:

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 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
...

On Sunday, September 14, 2008 11:18 AM
Dave Peterson wrote:

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

Li Jianyong wrote:

--

Dave Peterson

On Sunday, September 14, 2008 11:26 AM
franci wrote:

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 menuName Define Trange select
G2:G235 and run the code again.

regards, xlsops

"Li Jianyong" wrote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorials...evelopers.aspx

--

Dave Peterson