Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.match
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction.match returns error
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction.match returns error
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Re : Excel VBA and WorkSheetFunction (Match) | Excel Programming | |||
Application.WorksheetFunction.Match | Excel Programming | |||
Syntax for WorksheetFunction Match | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |