Using Ranges

[Guest]

Hi,

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

RangeName = "Sheet2!$C$4:$Q$"&COUNT(Sheet2!$E$4000)+3

So I want to write a macro that uses the range.
x = Range("RangeName").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

 

[Bernie Deitrick]

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 MVP