Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range scope question (using application.match)
XL2003, sheet names are sheet codenames not the tab names.
I have a named range on Sheet1 called "Positions" The following code in a code module returns the correct match, and evaluates to a value of 3 sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) The /exact/ same code, in a sub behind Sheet11 returns a 1004 runtime error. sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) '<-- crashes here sVal in both cases pulls the correct value. The error occurs when trying to calculate the the JobVal value. Is there some reason or rule that says a sheet can't use application.match on a named range from a different sheet? I use named ranges sometimes for data validation lists on other sheets, so I'd think it should be ok, but I can't figure out why this isn't working... or an appropriate workaround. I suppose I could call a sub in a module and put this value in a public variable, but I'd like to understand why it doesn't work in the first place! Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range scope question (using application.match)
sVal = Sheet11.Range("B5").Value
JobVal = Application.Match(sVal, Range("Position"), 0) Range("Position") in a sheet module is equivalent to me.Range("Position") since the named range position isn't in that sheet, you get an error. Try Application.Range("Position") instead. Or if that doesn't work then try thisworkbook.names("Position").ReferstoRange -- Regards, Tom Ogilvy "Keith" wrote: XL2003, sheet names are sheet codenames not the tab names. I have a named range on Sheet1 called "Positions" The following code in a code module returns the correct match, and evaluates to a value of 3 sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) The /exact/ same code, in a sub behind Sheet11 returns a 1004 runtime error. sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) '<-- crashes here sVal in both cases pulls the correct value. The error occurs when trying to calculate the the JobVal value. Is there some reason or rule that says a sheet can't use application.match on a named range from a different sheet? I use named ranges sometimes for data validation lists on other sheets, so I'd think it should be ok, but I can't figure out why this isn't working... or an appropriate workaround. I suppose I could call a sub in a module and put this value in a public variable, but I'd like to understand why it doesn't work in the first place! Thank you, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range scope question (using application.match)
Excellent- thank you Tom!!
"Tom Ogilvy" wrote in message ... sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) Range("Position") in a sheet module is equivalent to me.Range("Position") since the named range position isn't in that sheet, you get an error. Try Application.Range("Position") instead. Or if that doesn't work then try thisworkbook.names("Position").ReferstoRange -- Regards, Tom Ogilvy "Keith" wrote: XL2003, sheet names are sheet codenames not the tab names. I have a named range on Sheet1 called "Positions" The following code in a code module returns the correct match, and evaluates to a value of 3 sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) The /exact/ same code, in a sub behind Sheet11 returns a 1004 runtime error. sVal = Sheet11.Range("B5").Value JobVal = Application.Match(sVal, Range("Position"), 0) '<-- crashes here sVal in both cases pulls the correct value. The error occurs when trying to calculate the the JobVal value. Is there some reason or rule that says a sheet can't use application.match on a named range from a different sheet? I use named ranges sometimes for data validation lists on other sheets, so I'd think it should be ok, but I can't figure out why this isn't working... or an appropriate workaround. I suppose I could call a sub in a module and put this value in a public variable, but I'd like to understand why it doesn't work in the first place! Thank you, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range Scope | Excel Discussion (Misc queries) | |||
Deleting all named ranges that have a workbook scope | Excel Discussion (Misc queries) | |||
application.match question | Excel Programming | |||
Defined Name Range (Application.Match) | Excel Programming | |||
Application Event Scope Problem | Excel Programming |