Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    VBA - select Cell containing named range

    I thought this was going to be easy, but it isn't - or at least I have found an easy way to do it !

    The desired output of my VBA module is to select a cell when I know the Name it has been given. The code to find the Name is working perfectly and I can select the cell as long as it is on the active Worksheet.

    However a different Worksheet may be active when the VBA runs, so I need first to select the Worksheet then the Cell.

    How, in VBA, can I determine which Worksheet contains the Named Cell ?

    I have a workaround using a Worksheet formula but this is rather complex and I was hoping to be able to do it within VBA.

    Thanks,

    Martin

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    zWhichSheet = Range("RangeName").Worksheet.Name

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MartinM (2015-04-12)

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks RG - you were too quick for me . . . I had just worked it out and was going to post the solution when I saw your reply. That'll teach me to be more persistent before I ask for help.

    Speedy service for a sunny Sunday morning !

    PS Following Zeddy's example, I am lining things up so I can deliver your 1,000th "thank you" - round about July if things go on as they are

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Gee, I didn't know there was a contest going on!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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