Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Application.Goto Trick

  1. #1
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Cool Application.Goto Trick

    This is just a favorite trick that I've used in the past to avoid having to parse range references in VBA just so I can navigate to some range. I was working on a project this morning and the situation presented itself once again; figured I'd take a moment to share it.


    I have a range reference like this: "Apr!$L$72:$O$76"
    ...and I need my macro to goto or operate on that address.

    Assuming the string:
    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    
    
    If we try to use either of the following, the code fails:
    VB:
    Range(GotoAddr).Select 
    Application.Goto GotoAddr 
    
    
    So frequently (perhaps always?) you see people parsing the string address into seperate references for the sheet and then the range, this way they can select the sheet and with the sheet selected get to the range.

    For instance, either of the following would be acceptable to VBA:
    VB:
    Sheets("Apr").Range("$L$72:$O$76").Select 
    Application.Goto Reference:=Worksheets("Apr").Range("$L$72:$O$76") 
    
    
    ...but of course we have to parse the original string to break it up into the necessary components.

    There is a handy little trick I've used in the past to avoid the parsing. For whatever reason, the Goto method does allow R1C1 sheet/range references, it just doesn't like them in A1 format. We also have a nifty little command in VBA called "ConvertFormula" that we can use to easily switch between these two reference styles.

    So now comes the neat trick...
    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    Application.Goto Application.ConvertFormula(GotoAddr, xlA1, xlR1C1) 
     
     'or if you prefer...
    GotoAddr = "Apr!$L$72:$O$76" 
    GotoAddr = Application.ConvertFormula(GotoAddr, xlA1, xlR1C1) 
    Application.Goto GotoAddr 
    
    

    Granted, if you can get the range string reference in R1C1 format first, you don't even need to convert it.
    Last edited by Aaron Blood; January 8th, 2009 at 23:59.
    Sub All_Macros(Optional control As Variant)

  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Application.Goto Trick

    Thanks for sharing, Aaron

    I know you dislike Named Ranges, but I would use one or, the sheet CodeName so tab name change has no adverse effect;


    VB:
    Application.GoTo Range("MyRange") 
    
    
    VB:
    Application.GoTo Sheet1.Range("$L$72:$O$76") 
    
    
    BTW, rather than
    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    Range(GotoAddr).Select 
    Application.Goto GotoAddr 
    
    
    Try

    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    Application.Goto Range(GotoAddr) 
    
    

  3. #3
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Application.Goto Trick

    Yeah, it's not really a situation where range names are handy... I'm processing like several hundred string references. Actually, it's working from a dump of defined workbook names (insert|name|paste|pastelist) and it provides all the refersto addresses in A1 string format. I wanted to d-click a cell to follow the link, so I do stuff like this in the event procedure of the sheet so I can see where the names are.

    I haven't tried that last combination you suggested though. I'll maybe give that one a shot tomorrow. Seems like it shouldn't work... but who am I to say what's logical in VBA.
    Sub All_Macros(Optional control As Variant)

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Application.Goto Trick

    it's working from a dump of defined workbook names (insert|name|paste|pastelist) and it provides all the refersto addresses in A1 string format
    Why not read the Names themselves direct into GoTo rather than their address and sheet?

    The last combo, works, at least in 2007.

  5. #5
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Application.Goto Trick

    I didn't really want to get too far into discussing the app I'm working on so much as the concept... but it's a good question.

    It's a WB name manager worksheet, I dump the names in there, make a bunch of changes to em, sometimes I want to navigate to the changed reference before I actually assign it to the name.

    This just happened to be one situation where the issue cropped up again. I've had other situations where I've needed to navigate to a string range reference in this sheet!range format. Seems to me there are some objects that I've come across that return string addresses in this format...

    Oh and I did test your last suggestion:
    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    Application.Goto Range(GotoAddr) 
    
    
    As I suspected, it doesn't work (maybe they tweaked it a bit in 2007). Too bad, it would've been cleaner.
    That suggests to me that in 2007 you might be able to just use:
    VB:
    GotoAddr = "Apr!$L$72:$O$76" 
    Range(GotoAddr).Select 
    
    
    Is that true?
    Sub All_Macros(Optional control As Variant)

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Application.Goto Trick

    No, not really, the Apr sheet MUST be active for that syntax to work.

    I'm astounded to see 1 improvement in 2007 after mostly see steps backwards since upgrading

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 0
    Last Post: April 17th, 2008, 06:05
  2. Method 'goto' Of Object Application Failed
    By ayorabi in forum EXCEL HELP
    Replies: 3
    Last Post: December 17th, 2006, 04:05
  3. Excel Trick Help!!
    By rico2k_uk in forum EXCEL HELP
    Replies: 3
    Last Post: September 23rd, 2005, 07:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno