none
Create and Select Named Range in Excel RRS feed

  • Question

  • I'm using Office 365 ProPlus.

    It's been several years since I worked with Excel VBA, and now I am struggling to achieve something that seems like it should be easy. All I want to do is create a named range, and then later in the code I want to move the cursor to that range. The range will change every time the code is run, so it needs to be dynamic. I've got the code reliably selecting the correct cell. My problem seems to be with creating the range. Here is the command that I am using:

         ActiveWorkbook.Names.Add Name:="Next_Ticket", RefersToR1C1:=ActiveSheet.Name & "!" & ActiveCell.Address

    This results in a value like:

         'Tickets!$B$1464'

    Since that's a string value, the subsequent command to move the cursor to that cell fails.  The command that I am using to move the cursor is:

         Range("'" & ActiveSheet.Name & "'!" & "Next_Ticket").Activate

    How do I create the named range so that it references a range instead of a value?  Is there a better way to achieve my goal?

    Thanks in advance for any help that you can offer!

    --Tom

    Wednesday, June 14, 2017 6:47 PM

All replies

  • RefersToRange is what you are looking for but putting it all together

    Sub test()
    Dim nm As Name
    Dim rng As Range
    
        Set rng = ThisWorkbook.Worksheets("Sheet2").Range("b2:c3")
        Set nm = ThisWorkbook.Names.Add("myName", rng)
        
        ' more typically a different routine for future use
        Set rng = ThisWorkbook.Names("myName").RefersToRange
        rng.Parent.Parent.Activate ' the book
        rng.Parent.Activate ' the  sheet
        rng.Activate ' the range
    
    End Sub
    You'd wouldn't do it like that in real life as no need to refer to the name when you've already got the range, but adapt to your needs.

    Wednesday, June 14, 2017 7:47 PM
    Moderator