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
|