Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Dim x as Integer

x = Activesheet.Index

If x + 2 <= activeworkbook.sheets.count
Sheets(x+2).Select
End If

If x-2 0 then
Sheets(x-2).Select
End if

you could creat a variable to replace the "2"
--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

You need to put all the code in a standard module; not in the ThisWorkbook
module.

In the VBE (Visual Basic Editor); Insert | Module.

--

Vasant

"MichaelC" wrote in message
...
Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project
/
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option
explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I
have.
I get Run Time Error 424 ( text following a dot is not recognized as
object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I
had
originally seen the reference. Still strange that VBA help, nor for
that
matter John Walkenbach's excellent Power Programming book seems to
make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references
to it
anywhere.
Thanks in advance for all advice

I think the problem is J's function was intended to be entered as a worksheet
function, not called via VBA. The application.caller.parent is returning the
worksheet from which the function was called. This does not work if called
via VBA.

Try this. If calling the function via vba, use Range("A1") or maybe a range
object variable when calling the function. I believe you could still call
the function as a worksheet function, but leave the quotes out
=SHEETOFFSET(1,A1)



Sub Test()
ActiveCell = SHEETOFFSET(1, Range("A1"))
End Sub

Function SHEETOFFSET(offset, Ref)
'Returns cell contents at Ref, in sheet offset
Application.Volatile
With Ref
SHEETOFFSET = .Parent.Parent.Sheets(.Parent.Index + offset) _
.Range(Ref.Address).Value
End With
End Function


"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

As Vasant said - move the function code to a standard module. Your macros
will not find it in ThisWorkbook.

Thanks for catching that, Vasant!

"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Note in my previous post that I made changes to the function code. Copy and
paste into your module.


"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Thank you Vasant - I have done that and the same Runtime error message 424
occuring with yellow highlighting of the line: With Application.Caller.Parent
I can get the formula to work in a cell, but cannot get it to work in a VBA
module.

This is an extract of the code in the module:
Sub GoodLoopInteger8()
RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
StartVal = Sheets("Control").Cells(17, ColumnValue)
NumToFill = Sheets("Control").Cells(23, ColumnValue)
TradeAttribute = Sheets("Control").Cells(11, ColumnValue)

For Cnt = 0 To NumToFill - 1
' To select the appropriate sheet dependent on the RowValue of the ActiveCell
SHEETOFFSET(RowValue - 11, C4).Select
Range("VarInput").offset(Cnt, TradeAttribute).Value = StartVal + Cnt

Perhaps I am barking up the wrong tree - it's happened before!

"Vasant Nanavati" wrote:

You need to put all the code in a standard module; not in the ThisWorkbook
module.

In the VBE (Visual Basic Editor); Insert | Module.

--

Vasant

"MichaelC" wrote in message
...
Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project
/
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option
explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I
have.
I get Run Time Error 424 ( text following a dot is not recognized as
object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I
had
originally seen the reference. Still strange that VBA help, nor for
that
matter John Walkenbach's excellent Power Programming book seems to
make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references
to it
anywhere.
Thanks in advance for all advice

Vasant Nanavati wrote:
You need to put all the code in a standard module; not in the ThisWorkbook
module.

In the VBE (Visual Basic Editor); Insert | Module.


"MichaelC" wrote in message
...
Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA
Project / Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.


That user defined function (UDF) was designed to be called from a worksheet
cell, not from another VBA procedure. When you enter a UDF in a worksheet
function, Application.Caller will refer to the Range object (the cell) where
you entered the function. If you enter "=SHEETOFFSET(1,A1)" in Cell B2,
then

Application.Caller = the Range object that is also defined as Range("B2")
Application.Caller.Parent = the Worksheet object that is the Parent of the
Range
Application.Caller.Parent.Parent = the Workbook object that contains the
worksheet that contains the cell that contains the udf.

When you call this function from a VBA procedure, like Sub Test(),
Application.Caller returns an Error object, which doesn't have a parent.
That's the nature of the problem you are experiencing. While Vasant was
right that you should put this function and your test sub in a standard
module and not the ThisWorkbook module, that's not why you were getting the
error. (In fact, if the logic were correct, it would work just fine in the
ThisWorkbook module).

If you want to call this function from a vba procedure, you need to change
the logic of the function. If you want to call it from a cell, you need to
change your test procedure to

ActiveCell.Formula = "=SHEETOFFSET(1,A1)"

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thank you Dick. I have added your blog to my favourites and shall peruse it
daily.
Michael

"Dick Kusleika" wrote:

Vasant Nanavati wrote:
You need to put all the code in a standard module; not in the ThisWorkbook
module.

In the VBE (Visual Basic Editor); Insert | Module.


"MichaelC" wrote in message
...
Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA
Project / Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.


That user defined function (UDF) was designed to be called from a worksheet
cell, not from another VBA procedure. When you enter a UDF in a worksheet
function, Application.Caller will refer to the Range object (the cell) where
you entered the function. If you enter "=SHEETOFFSET(1,A1)" in Cell B2,
then

Application.Caller = the Range object that is also defined as Range("B2")
Application.Caller.Parent = the Worksheet object that is the Parent of the
Range
Application.Caller.Parent.Parent = the Workbook object that contains the
worksheet that contains the cell that contains the udf.

When you call this function from a VBA procedure, like Sub Test(),
Application.Caller returns an Error object, which doesn't have a parent.
That's the nature of the problem you are experiencing. While Vasant was
right that you should put this function and your test sub in a standard
module and not the ThisWorkbook module, that's not why you were getting the
error. (In fact, if the logic were correct, it would work just fine in the
ThisWorkbook module).

If you want to call this function from a vba procedure, you need to change
the logic of the function. If you want to call it from a cell, you need to
change your test procedure to

ActiveCell.Formula = "=SHEETOFFSET(1,A1)"

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

JMB, Vasant, Steve and Dick - thank you all very much. I will implement all
your valuable advice tomorrow after a little shuteye. I'd have given up a
long time ago were it not for your thoughtful patience.

"JMB" wrote:

Note in my previous post that I made changes to the function code. Copy and
paste into your module.


"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Good luck. Now that I've had some shuteye myself (and thinking more
clearly), if you want the function to show as a formula in Excel, do as Dick
suggested. If you want to do strictly in VBA use:

MsgBox Worksheets(ActiveSheet.Index + 1).Range("A1")

This would give you the flexibility to use a point of reference other than
the Activesheet (Say Sheet3.Index+1 -note I'm using the codename for the
sheet). The SHEETOFFSET function as I've modified assumes the activesheet is
the point of reference. Mr. Walkenbach probably did not design the function
to be called from either a worksheet or VBA because with VBA there's an
easier way.

I've got to catch a plane (literally) -so don't think me rude if I don't
post back today.



"MichaelC" wrote:

JMB, Vasant, Steve and Dick - thank you all very much. I will implement all
your valuable advice tomorrow after a little shuteye. I'd have given up a
long time ago were it not for your thoughtful patience.

"JMB" wrote:

Note in my previous post that I made changes to the function code. Copy and
paste into your module.


"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice

Thank you so much JMB. Have a safe flight.

"JMB" wrote:

Good luck. Now that I've had some shuteye myself (and thinking more
clearly), if you want the function to show as a formula in Excel, do as Dick
suggested. If you want to do strictly in VBA use:

MsgBox Worksheets(ActiveSheet.Index + 1).Range("A1")

This would give you the flexibility to use a point of reference other than
the Activesheet (Say Sheet3.Index+1 -note I'm using the codename for the
sheet). The SHEETOFFSET function as I've modified assumes the activesheet is
the point of reference. Mr. Walkenbach probably did not design the function
to be called from either a worksheet or VBA because with VBA there's an
easier way.

I've got to catch a plane (literally) -so don't think me rude if I don't
post back today.



"MichaelC" wrote:

JMB, Vasant, Steve and Dick - thank you all very much. I will implement all
your valuable advice tomorrow after a little shuteye. I'd have given up a
long time ago were it not for your thoughtful patience.

"JMB" wrote:

Note in my previous post that I made changes to the function code. Copy and
paste into your module.


"MichaelC" wrote:

Thanks for staying with me, JMB.
I'm not using quotes. I'm doing something else wrong.
I copied and pasted the function into a fresh worksheet under VBA Project /
Excel objects / ThisWorkbook.
Then I wrote:
Sub Test()
ActiveCell = SHEETOFFSET(1, A1)
End Sub

When I ran the Sub Test I got Run-time error 424 - Object required.

What did I do different from you when you successfully tested it?
I appreciate your patience.


"JMB" wrote:

I copied the code in exactly as you posted it and no problems.
Unfortunately, I'm unable to duplicate your error, even with option explicit.

For the range reference, are you using quotes or not?

=SHEETOFFSET(3,"A1")

or

=SHEETOFFSET(3,A1)

It has to be entered w/o quotes.

If you had to declare the argument variable types,

Function SHEETOFFSET(offset as integer, Ref as range) as variant


"MichaelC" wrote:

The function formula from J-Walk is:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

I cannot get this to work, probably due to some basic misconception I have.
I get Run Time Error 424 ( text following a dot is not recognized as object)

Is this something to do with Option Explicit? If that means I have to
declare my variables, what needs to be done?
I apologise for what I know are elementary questions.


"MichaelC" wrote:

Thank you both, gentlemen. The J-Walk reference is exactly where I had
originally seen the reference. Still strange that VBA help, nor for that
matter John Walkenbach's excellent Power Programming book seems to make any
reference to this extremely useful tool!

"JMB" wrote:

Try this link

http://j-walk.com/ss/excel/tips/tip63.htm

"MichaelC" wrote:

Is there such an animal as Sheet Offset (# of Sheets).
I once made a note when I saw it but now can find no references to it
anywhere.
Thanks in advance for all advice