software

Hi,

I did an insert --gt; name --gt; define: a range that would automatically update:

RangeName = quot;Sheet2!$C$4:$Q$quot;amp;COUNT(Sheet2!$E$4000) 3

So I want to write a macro that uses the range.
x = Range(quot;RangeNamequot;).Value
gives the following error:
Method Range of Object Global Failed

when i set a range in the worksheet using the upper left hand box the macro
works.
Do you know how to fix this problem.

Thanks

That won't work - it's just an invalid string value, not a range. You need to use a named range
whose definition actually returns a range, along the lines of

=OFFSET(Sheet2!$C$4,0,0,COUNT(Sheet2!$E$4000) 3,15)

But note that COUNT(Sheet2!$E$4000) can only return 0 or 1, since it is only one cell, which can be
empty or filled. Perhaps you mean something like

COUNT(Sheet2!$E$1:$E$4000)

In which case, the whole thing becomes

=OFFSET(Sheet2!$C$4,0,0,COUNT(Sheet2!$E$1:$E$4000) 3,15)

HTH,
Bernie
MS Excel MVPquot;Jeffquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I did an insert --gt; name --gt; define: a range that would automatically update:
gt;
gt; RangeName = quot;Sheet2!$C$4:$Q$quot;amp;COUNT(Sheet2!$E$4000) 3
gt;
gt; So I want to write a macro that uses the range.
gt; x = Range(quot;RangeNamequot;).Value
gt; gives the following error:
gt; Method Range of Object Global Failed
gt;
gt; when i set a range in the worksheet using the upper left hand box the macro
gt; works.
gt; Do you know how to fix this problem.
gt;
gt; Thanks
gt;
gt;
gt;
gt;
gt;
gt;