Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Range Name in VBA (XL2K SR-1)

    G'day all, I'm back after being unemployed for 4 months. God it feels good to be working again.

    Problem, How to define a Range name using VBA, which changes shape each time data is added. I have no problems defining the actual range using xldown etc, but have problem with the following, which was created using the Record Macro function.

    ActiveWorkbook.Names.Add name:="dbase", RefersToR1C1:= _
    "='REW EFF (2)'!R3C1:R24C13"

    How can I set a variable which contains the range, to replace "RefersToR1C1:= "='REW EFF (2)'!R3C1:R24C13"

    I have played around with 'With Selection' and setting a variable to the range, but I get a Range Name with = TRUE, or a syntax error. VBA HELP was not helpful, guess I did not know what to look up.

    Hope someone can help

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    I'm not positive exactly what you are asking, but will this get you started:

    <pre>Dim strAddress As String
    strAddress = """='REW EFF (2)'!R3C1:R24C13"""
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Not exactly.

    Every time I run the code, the range dimension will have changed. I need a method of redefining the range, each time I run the code. But I can't work out how to feed the range to the names command.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    If the object variable oRange is set to the range in question, then this should work:

    <pre>Dim strAddress As String
    strAddress = """='" & oRange.Parent.Name & "'!" & oRange.Address(True, True, xlR1C1) & """"
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Not sure how to use that, here is my code. Please advise how you would use the variable to replace the "RefersToR1C1" argument

    Dim rn As Variant
    '
    Worksheets("REW EFF (2)").Activate
    Range("A3").Select
    Selection.End(xlDown).Select
    rn = ActiveCell.Row
    Range(Cells(3, 1), Cells(rn, 13)).Select
    ActiveWorkbook.Names.Add name:="dbase", RefersToR1C1:= _
    "='REW EFF (2)'!R3C1:R24C13"

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Like this:

    <pre>Dim strAddress As String
    Dim oRange As Range
    Set oRange = Range(Worksheets("REW EFF (2)").Cells(3, 1), _
    Worksheets("REW EFF (2)").Cells(Worksheets("REW EFF (2)").Range("A3").End(xlDown).Row, 13))
    strAddress = """='" & oRange.Parent.Name & "'!" & oRange.Address(True, True, xlR1C1) & """"
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Beudy! (spoken with Mick Dundee Accent)

    Many thanks.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range Name in VBA (XL2K SR-1)

    This might not answer your direct question, but I think it answers your problem:

    Once you have the range selected, why not use the line:
    <pre>Selection.Name = "dbase"
    </pre>


    It will define the currently selected range as dbase. You don't need to use the add names collection at all, so don't need to worry about the refers to.

    Steve

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Without using Selection I sometimes use something like

    zLastRow = [a1].currentRegion.Rows.Coiunt
    temp = "a1:h" & zLastRow
    Range(temp).Name = "dBase"

    ..where h is the last column in the block etc.

    zeddy

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Range Name in VBA (XL2K SR-1)

    OOps - typo, should've been

    zLastRow = [a1].currentRegion.Rows.Count


    zeddy

  11. #11
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    Thanks Jan, I already use your name utility. Its great.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name in VBA (XL2K SR-1)

    <P ID="edit" class=small>(Edited by Leif on 13-Mar-04 17:06. to update link to http://www.bmsltd.ie/)</P>Assuming the rows below your range are all empty.

    You can define a dynamic range name using this as the refersto formula:


    =OFFSET('REW EFF (2)'!$A$3,0,0,COUNTA('REW EFF (2)'!A:A)-COUNTA('REW EFF (2)'!$A$1:$A$2),13)



    This avoids having to redefine the range every time.

    BTW:

    download my name manager from http://www.bmsltd.ie/mvp!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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