Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
Syntax for WorksheetFunction | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |