Syntax for WorksheetFunction Match
 

Hi,

This is not working, it should work, I am pretty sure it is to do wit
syntax of array reference.

Sub FindISIN()

Worksheets(NowSheetName).Activate
NowSheetHeader = Range("NowHeader").Address

Bond1_Country_Pos = Application.WorksheetFunction.Match("Level2"
NowSheetHeader, 0)

End Sub

NowSheetName is worksheet name, defined in as Public Const at top.
"NowHeader" is dynamic named range in sheet that is 1rXc array.
think problem is with NowSheetHeader, which currently return
"$A$1:$W$1", but should I have sheet name in there as well, even thoug
this is active.

Match function does work if you replicate in worksheet - i.e. "Level2
is definately in the range.

Thanks in advanc

--
Message posted from http://www.ExcelForum.com

Syntax for WorksheetFunction Match
 

"Hardy",

I think the problem is that the second argument of the
Match function ha to be a range, so your string is
rejeceted. To ways round this would be to use:

=Application.WorksheetFunction.Match("Level2",Rang e
(NowSheetHeader), 0)

As you rightly observe, this will cause an error if the
sheet with your range in it isn't active. To get round
this, use:

= Application.WorksheetFunction.Match("Level2",Range
("NowHeader"), 0)

Cheers, Pete.

-----Original Message-----
Hi,

This is not working, it should work, I am pretty sure it
is to do with
syntax of array reference.

Sub FindISIN()

Worksheets(NowSheetName).Activate
NowSheetHeader = Range("NowHeader").Address

Bond1_Country_Pos = Application.WorksheetFunction.Match
("Level2",
NowSheetHeader, 0)

End Sub

NowSheetName is worksheet name, defined in as Public
Const at top.
"NowHeader" is dynamic named range in sheet that is 1rXc
array. I
think problem is with NowSheetHeader, which currently
returns
"$A$1:$W$1", but should I have sheet name in there as
well, even though
this is active.

Match function does work if you replicate in worksheet -
i.e. "Level2"
is definately in the range.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

.