VB Syntax for OFFSET statement

[programmernovice]

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.

Can someone please help me with this? Many thanks.

 

[Jake Marx]

Hi,

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.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Try

Names("rows").Value

to get 3 out of your name

--
Stefano Gatto

Hi,

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.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[programmernovice]

Try

Names("rows").Value

to get 3 out of your name

--
Stefano Gatto

Hi,

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.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Thanks, Jake and Stefano,you all are very helpful, that's what I
needed.

 

[programmernovice]

Hi,



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

 

[Jake Marx]

Hi Bathan,

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

There are a few things you could try to rectify this. Try each step below
to see if one will work for you:

1) If you're using a button to launch the code, make sure the
TakeFocusOnClick property of the CommandButton is set to False.

2) Ensure that the "rows" named range has an integer value of >= 0. From
the Immediate Window:

?Range("rows").Value

3) Does this command give you the correct cell reference:

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

If so, then the Select is failing because you're trying to select a Range on
an inactive Worksheet. I don't think this is the problem, as the error
indicates a problem with the Range itself, not the selection. That said, if
you can remove the .Select, you should. Most things in Excel can be done
without activating or selecting anything.

4) Try fully-qualifying your Range reference:

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

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[programmernovice]

Hi Bathan,



There are a few things you could try to rectify this. Try each step below
to see if one will work for you:

1) If you're using a button to launch the code, make sure the
TakeFocusOnClick property of the CommandButton is set to False.

2) Ensure that the "rows" named range has an integer value of >= 0. From
the Immediate Window:

?Range("rows").Value

3) Does this command give you the correct cell reference:

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

If so, then the Select is failing because you're trying to select a Range on
an inactive Worksheet. I don't think this is the problem, as the error
indicates a problem with the Range itself, not the selection. That said, if
you can remove the .Select, you should. Most things in Excel can be done
without activating or selecting anything.

4) Try fully-qualifying your Range reference:

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

Thanks for replying, Jake. I should point out that if I write the
statement:

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

the macro works fine. Only when I substitue Range("rows") for 3 as the
first argument of Offset do I get the error message. Does this give
some indication of where the problem is? Thanks again for all your
help.

 

[Jake Marx]

Hi Bathan,

Thanks for replying, Jake. I should point out that if I write the
statement:

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

the macro works fine. Only when I substitue Range("rows") for 3 as
the first argument of Offset do I get the error message. Does this
give some indication of where the problem is? Thanks again for all
your help.

What does this give you when run from the Immediate Window:

?Range("rows").value

If it doesn't give you 3 (or whatever value you're expecting), then you have
either mistyped the named range or the named range refers to something else.
You can check that by trying this:

?Range("rows").Parent.Name
?Range("rows").Address

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[programmernovice]

Hi Bathan,



What does this give you when run from the Immediate Window:

?Range("rows").value

I get :
Run-time error '1004'
Application-defined or object-defined error.

If it doesn't give you 3 (or whatever value you're expecting), then you have
either mistyped the named range or the named range refers to something else.
You can check that by trying this:

?Range("rows").Parent.Name
?Range("rows").Address

I get the same error message. If I enter "=rows" into any cell of the
worksheet I do get "3", which is what I have defined "rows" to refer
to.

Thanks again for your help.

 

[Jake Marx]

Hi Bathan,

I get :
Run-time error '1004'
Application-defined or object-defined error.

If I enter "=rows" into any cell of the
worksheet I do get "3", which is what I have defined "rows" to refer
to.

OK. It may be that you defined the name to be a worksheet-level name. In
that case, try this:

?Worksheets("Sheet1").Range("rows").Value

[replace Sheet1 with the name of the worksheet your named range resides on]

If this gives you 3, then you have a worksheet-level name and not a global
(workbook-level) name. No problem, but you'll need to specify the worksheet
whenever you refer to it in your code.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]