Hi.

I have a VBA macro that takes a range as an input parameter. It does
things to cells in that range, then returns some interger value.

A cell on a worksheet calls this macro and passes the range in as a
named range. Note that the macro does not refer to the name, it just
takes the range as input.

The macro works great as long as the worksheet with the cell that
calls the macro is active. If I make a different worksheet active
(worksheet B), I find that when I return to the worksheet with the
cell that calls the macro (worksheet A), the value returned by the
macro is based on worksheet B. Once I do something to worksheet A,
the forumla gets called again, and the problem gets corrected.

The named range I pass to the macro is defined in relative terms (ie.,
with dollar signs) and is prefaced with the worksheet name that
contains the range. Here's what the ranges look like:

AssignedToHeading ='UC Scenario Status'!$M$6
AssignedToStart =OFFSET(AssignedToHeading,1,0)
AssignedToEnd =OFFSET('UC Scenario Status'!$M$25,-1,0)
AssignedToRange =AssignedToStart:AssignedToEnd

"AssignedToRange" is what I'm passing to the macro. The worksheet
with the range is "UC Scenario Status". That also contains the cell
that calls the macro.

So what's happening is that the worksheet name (UC Scenario Status)
seems to be getting ignored in the macro. What do I need to do to get
it noticed?

Thanks!

Ken