Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    Naming ranges using VBA

    Hi folks,

    My users get exported data out of another application. In the exported worksheet I need to name 7 ranges.

    The row number where these ranges begin changes with each export, but the column numbers are always the same. My current solution is to ask the users (via a VBA form) for the number of the first row. Using that info, I'd like to name the range but I can't figure out the right/best way to do so (I should probably mention that the OFFSET function has always confused the heck out of me, which is probably why this is giving me fits).

    Currently I build a string, where EC1 is the row number specified by the user. Q is the column where the first range resides:

    DiBegin = "Q" & CStr(EC1)

    The end of the range is always 6 rows down, so I build a string representing the end of the range:
    EC1End = EC1 + 5
    DiEnd = "Q" & CStr(EC1End)

    Then I add the named range:

    Names.Add Name:="DiclofenacEC1", RefersTo:="Processed!DiBeginiEnd"

    It's working...except in the worksheet it tells me the name refers to the string, rather than the cells. I thought the code would be smart enough to interpret DiBegin and DiEnd as the cells represented by the strings, but no. This is copied from the Name Manager in Excel:

    ="Processed!DiBeginiEnd"

    Clearly I've completely screwed this up. Feel free to point and laugh.

    Thanks in advance for the help.

    Beej

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

    You can actually set it up with a single statement as follows:
    Code:
               ActiveWorkbook.Names.Add Name:= zNewRngName, RefersToR1C1:= _
                 "=OFFSET(" & zNewSht & "!R" & Format(EC1) &"C16,0,0,COUNTA(" & _
                              zNewSht & "!R"& Format(EC1) & "C1:R[100]C1),1)"
    Where:
    zNewRngName = a string type variable holding your desired range name.
    zNewSht = a string type variable holding the sheet name or you can hard code it if it doesn't change.
    EC1 = a long type variable holding the row number {if it is a string you can drop the format function}
    100 = is the maximum rows in the range but will only use those it counts from the row in EC1 until it finds a blank which in your case should be the 7th row down.

    Note this creates a single column range. To make it multiple cols wide change the ,1) at the end of the 3rd line to ,n)

    Where: n = number of columns in the range. 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:

    Beej (2013-02-28)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Nice single statement.
    But I hate R1C1 formatting, especially in code.
    It does my head in.
    I prefer something direct and simple like:

    Code:
    temp = "q" & EC1 & ":q" & (EC1+5)	'e.g. "q17:q22"
    range(temp).name = "DiclofenacEC1"
    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Beej (2013-02-28)

  6. #4
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Thank you both! I love this forum.

  7. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by zeddy View Post
    Hi RG

    But I hate R1C1 formatting, especially in code.
    It does my head in.
    .. as opposed to letters for column numbers? Ick! Base 26 doesn't lend itself to doing math on cell addresses. Since everything internally uses R1C1 notation, why not leave it that way?

    Of course, I know where the use of letters originated, and it wasn't at Microsoft. So far as I know, that numbering scheme first appeared in VisiCalc and its immediate descendant, Lotus 1-2-3, which caught the attention of accountants and financial analysts, and the rest is history.

    The first Microsoft spreadsheet program of which I am aware was MultiPlan, a DOS program, and it used the much more intuitive (to me) R1C1 notation.

    Of course, I've adjusted to using the VisiCalc/Lotus 1-2-3 cell numbering notation, but I still find myself doing handstands every time I need to convert the location of a cell for use in a VBA function.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by TXWizard View Post
    I still find myself doing handstands every time I need to convert the location of a cell for use in a VBA function.
    may I ask why?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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