Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Hi,
I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
I would think with text, you'd be looking for an exact match.
I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Hi Dave,
I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Is there some way of wrapping the code in the window so that I can see the
entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Hi Chip,
Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Try dropping the .worksheetfunction from that line.
Just use application.vlookup(...) DarrellK wrote: Hi Chip, Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
download mz tools, that's one of it's features
http://www.mztools.com/index.htm -- Gary "DarrellK" wrote in message ... Hi Chip, Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Your suggestion does work. However, I am confused. When am I supposed to use
application.worksheetfunction.vlookup(...) and when am I supposed to use application.vlookup(...)? What is the rationale? Thanks. Darrell "Dave Peterson" wrote: Try dropping the .worksheetfunction from that line. Just use application.vlookup(...) DarrellK wrote: Hi Chip, Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
application.match() and application.worksheetfunction.match()
and application.vlookup() and application.worksheetfunction.vlookup() work the same way. I don't have any idea why it was implemented this way, but you have a couple of options: ==== saved from a previous post. Dim res1 as variant 'may return an error (like #n/a in the worksheet dim res2 as variant dim myStr as string res1 = application.vlookup(xcombo.value, searchrange, 3, false) res2 = application.vlookup(ycombo.value, searchrange, 5, false) if iserror(res1) _ or iserror(res2) then mystr = "Some kind of error with vlookup" elseif isnumeric(res1) _ and isnumeric(res2) then mystr = format(res1 + res2, "#,###.00") 'or no format??? else mystr = "at least one non-numeric found" end if nonfincalcLabel.Caption = mystr ==== Untested, uncompiled. Watch for typos. Application.vlookup() returns an error that you can test with iserror(). application.worksheetfunction.vlookup() causes a runtime error that you have to catch. dim res as variant on error resume next res = application.worksheetfunction.vlookup(....) if err.number < 0 then 'an error was found err.clear else 'no error! end if on error goto 0 application.vlookup() looks/works much easier (well, to me). DarrellK wrote: Your suggestion does work. However, I am confused. When am I supposed to use application.worksheetfunction.vlookup(...) and when am I supposed to use application.vlookup(...)? What is the rationale? Thanks. Darrell "Dave Peterson" wrote: Try dropping the .worksheetfunction from that line. Just use application.vlookup(...) DarrellK wrote: Hi Chip, Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Match" function returns wrong value
Hi Dave,
Just wanted to say thank you for all your help. Darrell "Dave Peterson" wrote: application.match() and application.worksheetfunction.match() and application.vlookup() and application.worksheetfunction.vlookup() work the same way. I don't have any idea why it was implemented this way, but you have a couple of options: ==== saved from a previous post. Dim res1 as variant 'may return an error (like #n/a in the worksheet dim res2 as variant dim myStr as string res1 = application.vlookup(xcombo.value, searchrange, 3, false) res2 = application.vlookup(ycombo.value, searchrange, 5, false) if iserror(res1) _ or iserror(res2) then mystr = "Some kind of error with vlookup" elseif isnumeric(res1) _ and isnumeric(res2) then mystr = format(res1 + res2, "#,###.00") 'or no format??? else mystr = "at least one non-numeric found" end if nonfincalcLabel.Caption = mystr ==== Untested, uncompiled. Watch for typos. Application.vlookup() returns an error that you can test with iserror(). application.worksheetfunction.vlookup() causes a runtime error that you have to catch. dim res as variant on error resume next res = application.worksheetfunction.vlookup(....) if err.number < 0 then 'an error was found err.clear else 'no error! end if on error goto 0 application.vlookup() looks/works much easier (well, to me). DarrellK wrote: Your suggestion does work. However, I am confused. When am I supposed to use application.worksheetfunction.vlookup(...) and when am I supposed to use application.vlookup(...)? What is the rationale? Thanks. Darrell "Dave Peterson" wrote: Try dropping the .worksheetfunction from that line. Just use application.vlookup(...) DarrellK wrote: Hi Chip, Thanks. I may try that. I have another problem which just occured. I am getting an "Unable to get Vlookup property of the worksheet function class" error with the following line of code: Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False) Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns F through CO in rows 1 through 363. Thanks. Darrell ------------------ "Chip Pearson" wrote: Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? There is no autowrap feature. You can, however, split one logical line of code into several separate physical lines of code using the <space<underscore character sequence at the end of a physical line of code. E..g, Range _ ("A1") _ ..Value _ = _ 123 is seen by VBA as Range("A1").Value = 123 Note that there must be a space preceding the underscore. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DarrellK" wrote in message ... Hi Dave, I cannot believe I made such a basic error. I do know that to get an exact match it is necessary to specify a "0" in the third field of the Match function as I do this all the time in Excel spreadsheet cells, but I guess the long code and the fact that I cannot see the entire line in the window, due to its length, caused me to make this mistake. Thanks again for pointing out this basic error. Is there some way of wrapping the code in the window so that I can see the entire code without having to scroll side to side? Or is it better to leave it as it is? Thanks again, Darrell "Dave Peterson" wrote: I would think with text, you'd be looking for an exact match. I'd use something like: Sub testmatch() dim res as variant res = application.match("Invision", _ Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A "), 0) if iserror(res) then msgbox "Not found" else msgbox res end if End Sub Notice the extra 0 in the =match() function. ps. You don't need to activate the workbook for this to work. And it's better to include the extension (assuming the file has been saved). DarrellK wrote: Hi, I am trying to simply return the position of a value in an array using the "Match" function and I seem to be getting an incorrect result. I am using the following code: Sub testmatch() Workbooks("Book1").Activate MsgBox (Application.WorksheetFunction.Match("Invision", Workbooks("Book1").Sheets("Sheet1").Range("A:A"))) End Sub "Invision" is in row 9 of column A. However, the MsgBox displays 284. There are entries in rows 2 through 363 if that is of any help. I would be very grateful for any assitance you could provide. I am using Visual Basic 6.3 and MS Excel 2003 SP2. Thanks. Darrell -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!! | Excel Worksheet Functions | |||
Multiple Offset/Match formula returns "#N/A" | Excel Discussion (Misc queries) | |||
Finding cell that returns bad "solver_val" function. | Excel Discussion (Misc queries) | |||
SUM Calculations Returns #VALUE but displays correct total with "Insert Function"=fx | Excel Worksheet Functions | |||
Excel "mode" function returns different results based on sort orde | Excel Worksheet Functions |