View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Keith Keith is offline
external usenet poster
 
Posts: 55
Default 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