Named range scope question (using application.match)

[Keith]

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

 

[Tom Ogilvy]

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

 

[Keith]

Excellent- thank you Tom!!

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