Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible? - Problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible? - Problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Offset - is this possible? - Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to look up ref in one sheet and offset in another | Excel Worksheet Functions | |||
Copy link to offset sheet | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset in another sheet | Excel Discussion (Misc queries) | |||
Using offset more than once on the same sheet | Excel Worksheet Functions |