Using Match to find value on worksheet
I am importing an XML file into a workbook. I then need to find
"bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
Using Match to find value on worksheet
Since it returns just one value, Match needs a one-dimensional array, yours
is two-dimensional. Here is a UDF that might help you. In A1 I have the word "Cherry" and on Sheet2 I have many words in the range A1:D100. The UDF tell me the address of the cell holding "Cheery" when called with =WHEREIS(A1,Sheet2!A1:D100) Function whereis(myword, myrange) For Each mycell In myrange If mycell.Value = myword Then whereis = mycell.Address Exit Sub End If Next End Function To get R1C1 style use mycell.Address(ReferenceStyle:=xlR1C1) Unfamiliar with VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
Using Match to find value on worksheet
I added "Exit Sub" as an afterthought; it should, of course, read "Exit
Function" -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
Using Match to find value on worksheet
Before I got your last reply, it had occured to be it should be End Function.
And then it worked. Many thanks for your help. It does precisely what I needed. "Bernard Liengme" wrote: I added "Exit Sub" as an afterthought; it should, of course, read "Exit Function" -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
All times are GMT +1. The time now is 06:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com