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

I really think it would be better if you posted the code rather than
expecting us to work it out from a brief explanation.

It will probably be something to do with trying to refer to data on the
activesheet, but having selected another, but can't be more specific at this
stage.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in message
om...
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

Your terminology seems erroneous to me. A 'named range' (Lotus 123
terminology) in Excel usually means a defined name i.e. a Name object.
You seem to be using 'named range' to mean a range's address e.g. 'UC
Scenario Status'!$M$6 is a range address. And the dollar signs in the
above example signify an absolute reference, not a relative reference.
Are your AssignedToRange etc variables Dim'd as type Excel.Range?

--

(Ken) wrote in message . com...

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.

"Bob Phillips" wrote in message ...
I really think it would be better if you posted the code rather than
expecting us to work it out from a brief explanation.

It will probably be something to do with trying to refer to data on the
activesheet, but having selected another, but can't be more specific at this
stage.


I found the answer at an excel related web site
(http://www.j-walk.com/ss/excel/tips/tip20.htm). I need to have a
"fully qualified" reference to my range. IOW, instead of passing the
range to the macro as MyRange, I need to pass it in as
'MyWorkBookName.xls'!MyRange.

See ya,

Ken

Which is what I was alluding to.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in message
om...
"Bob Phillips" wrote in message
...
I really think it would be better if you posted the code rather than
expecting us to work it out from a brief explanation.

It will probably be something to do with trying to refer to data on the
activesheet, but having selected another, but can't be more specific at
this
stage.


I found the answer at an excel related web site
(http://www.j-walk.com/ss/excel/tips/tip20.htm). I need to have a
"fully qualified" reference to my range. IOW, instead of passing the
range to the macro as MyRange, I need to pass it in as
'MyWorkBookName.xls'!MyRange.

See ya,

Ken