ExcelBanter - View Single Post

[email protected]

Jake Marx wrote:
Hi,

wrote:
Hi, I have defined a name in a worksheet to refer to a number. For
example the name "rows" refers to 3. How can I use the name within an
OFFSET statement in a VB macro? I have tried different forms of
OFFSET("rows",7) or OFFSET("=rows",7), or OFFSET(range("rows"),7),
etc. Nothing seems to work.

Range("rows").Value should work fine. But Offset is a method that must be
applied to an object - in this case a Range object. So your code would be
something like this:

MsgBox Range("A10").Offset(Range("rows").Value, 0).Address

Should give you A13.

Hi Jake, I tried

Range("$A$1").Offset(Range("rows").value,0).Select

and got the error message:

Run-time error '1004'
Method 'Range' of object '_Global' failed.

What did I do wrong? many thanks