Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
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. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Try
Names("rows").Value to get 3 out of your name -- Stefano Gatto "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. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Stefano Gatto wrote: Try Names("rows").Value to get 3 out of your name -- Stefano Gatto "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. -- 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Jake Marx wrote: Hi Bathan, wrote: 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).Addre ss 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Hi Bathan,
wrote: 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] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Jake Marx wrote: Hi Bathan, wrote: 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 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Syntax for OFFSET statement
Hi Bathan,
wrote: 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. <snip 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if statement syntax help | Excel Worksheet Functions | |||
need help with syntax of if statement | Excel Worksheet Functions | |||
VB Excel syntax for "offset" statement | Excel Programming | |||
Excel97 - Syntax for Find, Offset, Insert Page | Excel Programming | |||
OFFSET Syntax | Excel Programming |