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