Offset argument 
Author Message
 Offset argument

The Excel Offset instruction only accepts constant argument. For instance,
Range("A1").Offset(0,1) will work fine.
But the following, which uses variable arguments, will not :

RowOff = 2
ColOff=2
Range("A1").Offset(RowOff, Coloff)

Is there anyway of getting around this ?

IM



Fri, 28 Oct 2005 14:25:26 GMT  
 Offset argument
The Offset property can certainly take variable arguments. For
example,

Dim R As Long
Dim C As Long
R = 3
C = 4
Range("A1").Offset(R, C).Select

Specifically what problem are you having?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC


Quote:
> The Excel Offset instruction only accepts constant argument. For
instance,
> Range("A1").Offset(0,1) will work fine.
> But the following, which uses variable arguments, will not :

> RowOff = 2
> ColOff=2
> Range("A1").Offset(RowOff, Coloff)

> Is there anyway of getting around this ?

> IM



Fri, 28 Oct 2005 14:34:50 GMT  
 Offset argument
On Mon, 12 May 2003 23:25:26 +1000, "IM"

Quote:

>The Excel Offset instruction only accepts constant argument. For instance,
>Range("A1").Offset(0,1) will work fine.
>But the following, which uses variable arguments, will not :

>RowOff = 2
>ColOff=2
>Range("A1").Offset(RowOff, Coloff)

>Is there anyway of getting around this ?

>IM

It should work... are you declaring your variables?
Dim RowOff as integer
Dim Coloff as integer


Fri, 28 Oct 2005 15:51:28 GMT  
 Offset argument
Sorry, guys. I did put up the wrong question. What I claimed did not work
DID work. It was my attempt to simply the question that led to this.

The real question is in the following code which is used to define a dynamic
named range.

ActivieWorkbook.Names.Add Names:="Date", RefersToR1C1:="=OFFSET(R1C1,
1,0,COUTA(C1),1)"

THIS WORKED. But the following DIDN"T:

RowOff = 1
ColOff=0

ActivieWorkbook.Names.Add Names:="Date", RefersToR1C1:="=OFFSET(R1C1,
RowOff,ColOff,COUTA(C1),1)"

I did monitor the local window and did see the correct values of RowOff and
ColOff. No diagnostics were given, just the range "Date" just was NOT
defined properly.

All I wanted to do was to use  variables instead of constants in OFFSET part
of the last line of the above code.

Perhaps I could ask another question : How to use a variable to replace the
first argument (R1C1) for the OFFSET function ?

IM


Quote:
> On Mon, 12 May 2003 23:25:26 +1000, "IM"

> >The Excel Offset instruction only accepts constant argument. For
instance,
> >Range("A1").Offset(0,1) will work fine.
> >But the following, which uses variable arguments, will not :

> >RowOff = 2
> >ColOff=2
> >Range("A1").Offset(RowOff, Coloff)

> >Is there anyway of getting around this ?

> >IM

> It should work... are you declaring your variables?
> Dim RowOff as integer
> Dim Coloff as integer



Sat, 29 Oct 2005 12:37:42 GMT  
 Offset argument
Maybe I could be less confusing by including the following macro:

-----------------------------------------------------------
Public Sub test()
Dim r As Long, c As Long
    r = 5
    c = 6
    ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
       "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"
'   ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
'       "=OFFSET(Sheet1!R1C1,5,6,COUNTA(C1),1)"
        Range("Date").Select

End Sub
----------------------------------------------------------------------------

There are two statements beginning with "ActiveWorkbokk.Names........". One
of them is commented out.

When the above macro is run, one doesn't get the correct range selected.
Changing the statements r =  , c=.., to other values result in the SAME
range (wrong) selected each time.

However, on using the commented out statement instead, and changing the
pairs of values 5,6 to other values, the macro works fine.

Hence I have concluded that OFFSET only works fine if its second and third
argements are constants, not variables.

Could this be a bug of Excel ?

IM


Quote:
> The Excel Offset instruction only accepts constant argument. For instance,
> Range("A1").Offset(0,1) will work fine.
> But the following, which uses variable arguments, will not :

> RowOff = 2
> ColOff=2
> Range("A1").Offset(RowOff, Coloff)

> Is there anyway of getting around this ?

> IM



Sat, 29 Oct 2005 16:02:25 GMT  
 Offset argument
When you are assigning a formula string, all you are doing is
passing a string of text.  VBA doesn't recognize that there are
variables within the string. So in your code,

Quote:
>     ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
>        "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"

You are just passing the string of characters
"=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"
to the RefersTo property -- VBA doesn't and can't "know" that r
and c are actually varaibles.

You need to split the string out, such that r and c will be
recognized as varaibles, not just characters in the text string:

ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
       "=OFFSET(Sheet1!R1C1," & r & "," & c & ",COUNTA(C1),1)"

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC


Quote:
> Maybe I could be less confusing by including the following
macro:

> -----------------------------------------------------------
> Public Sub test()
> Dim r As Long, c As Long
>     r = 5
>     c = 6
>     ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
>        "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"
> '   ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
> '       "=OFFSET(Sheet1!R1C1,5,6,COUNTA(C1),1)"
>         Range("Date").Select

> End Sub
> ----------------------------------------------------------------
------------

> There are two statements beginning with

"ActiveWorkbokk.Names........". One

- Show quoted text -

Quote:
> of them is commented out.

> When the above macro is run, one doesn't get the correct range
selected.
> Changing the statements r =  , c=.., to other values result in
the SAME
> range (wrong) selected each time.

> However, on using the commented out statement instead, and
changing the
> pairs of values 5,6 to other values, the macro works fine.

> Hence I have concluded that OFFSET only works fine if its second
and third
> argements are constants, not variables.

> Could this be a bug of Excel ?

> IM



> > The Excel Offset instruction only accepts constant argument.
For instance,
> > Range("A1").Offset(0,1) will work fine.
> > But the following, which uses variable arguments, will not :

> > RowOff = 2
> > ColOff=2
> > Range("A1").Offset(RowOff, Coloff)

> > Is there anyway of getting around this ?

> > IM



Sat, 29 Oct 2005 16:08:10 GMT  
 Offset argument
Hi Chip

Many thanks for the reply. It certainly works. Wish I had posted earlier -
that would have saved me many hours trying to figure out what's wrong , but
to no avail until you came to the rescue.

It certainly is a tricky point that you raised and a good lesson for someone
like me whose understanding of fundamental Excel concepts is rather
marginal.

Thanks again.

IM


Quote:
> When you are assigning a formula string, all you are doing is
> passing a string of text.  VBA doesn't recognize that there are
> variables within the string. So in your code,

> >     ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
> >        "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"

> You are just passing the string of characters
> "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"
> to the RefersTo property -- VBA doesn't and can't "know" that r
> and c are actually varaibles.

> You need to split the string out, such that r and c will be
> recognized as varaibles, not just characters in the text string:

> ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
>        "=OFFSET(Sheet1!R1C1," & r & "," & c & ",COUNTA(C1),1)"

> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC



> > Maybe I could be less confusing by including the following
> macro:

> > -----------------------------------------------------------
> > Public Sub test()
> > Dim r As Long, c As Long
> >     r = 5
> >     c = 6
> >     ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
> >        "=OFFSET(Sheet1!R1C1,r,c,COUNTA(C1),1)"
> > '   ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
> > '       "=OFFSET(Sheet1!R1C1,5,6,COUNTA(C1),1)"
> >         Range("Date").Select

> > End Sub
> > ----------------------------------------------------------------
> ------------

> > There are two statements beginning with
> "ActiveWorkbokk.Names........". One
> > of them is commented out.

> > When the above macro is run, one doesn't get the correct range
> selected.
> > Changing the statements r =  , c=.., to other values result in
> the SAME
> > range (wrong) selected each time.

> > However, on using the commented out statement instead, and
> changing the
> > pairs of values 5,6 to other values, the macro works fine.

> > Hence I have concluded that OFFSET only works fine if its second
> and third
> > argements are constants, not variables.

> > Could this be a bug of Excel ?

> > IM



> > > The Excel Offset instruction only accepts constant argument.
> For instance,
> > > Range("A1").Offset(0,1) will work fine.
> > > But the following, which uses variable arguments, will not :

> > > RowOff = 2
> > > ColOff=2
> > > Range("A1").Offset(RowOff, Coloff)

> > > Is there anyway of getting around this ?

> > > IM



Sat, 29 Oct 2005 16:19:26 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Passing arguments to my offset formula

2. OFFSET with array 2nd or 3rd argument

3. OFFSET with array 2nd or 3rd argument

4. if activecell.offset()=activecell.offset

5. How to prevent getting an error ModName: unknown ModVer: 0.0.0.0 Offset: 001e493a

6. Time stamp 6 hours offset

7. Speed problem using Offset in VBA

8. Sum, Offset & Calculation

9. Offset,

10. Offset


 
Powered by phpBB® Forum Software © phpBB Group