Using Match to find value on worksheet

[Dkline]

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.

 

[Bernard Liengme]

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
A1100. The UDF tell me the address of the cell holding "Cheery" when
called with =WHEREIS(A1,Sheet2!A1100)

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

I added "Exit Sub" as an afterthought; it should, of course, read "Exit
Function"

 

[Dkline]

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.