Syntax for WorksheetFunction Match

H

Hardy

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
 
P

Pete McCosh

"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",Range
(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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top