Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name = RowHeader_ColumnHeader (97)

    This one seems so simple but I think I am stupid or something. Every time I think I have this beaten, it beats me up instead. HELP!

    For discussion take a 4x4 cell region with values as follows:
    <font color=blue>
    [empty] ["APPLE"] ["BANANA"] ["CHERRY"]
    ["RED"] [11] [12] [13]
    ["YELLOW"] [21] [22] [23]
    ["BLUE"] [31] [32] [33]
    <font color=black>

    What I want to do is loop thru every cell and create a name for each cell so that
    Range( "B2" ) is named "RED_APPLE"
    Range( "C4" ) is named "YELLOW_CHERRY"
    Range( "D3" ) is named "BLUE_BANANA"
    etc

    <font color=red>
    I need this to work on any range location/size since the input will be a range variable defined as
    xxyyTable = ActiveCell.CurrentRegion

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

    Re: Name = RowHeader_ColumnHeader (97)

    If your table is in cells A14, then try this:

    <pre>Public Sub NameCells()
    Dim oCell As Range
    With Worksheets("Sheet1")
    For Each oCell In .Range("B24")
    oCell.Name = .Range("A1").Offset(oCell.Row - 1, 0).Value & "_" & _
    .Range("A1").Offset(0, oCell.Column - 1).Value
    Next oCell
    End With
    End Sub
    </pre>

    Legare Coleman

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

    Re: Name = RowHeader_ColumnHeader (97)

    Note you can also use the standard Insert, Name, Create menu option with both Top Row and Left Column chekced.
    Now you can use the INTERSECT operator (a single space) to point to combinations:

    =RED APPLE
    yields 11
    =YELLOW CHERRY
    Yields 23
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Name = RowHeader_ColumnHeader (97)

    In Tools-Options-Calculation if you check the box in lower right corner labelled 'Accept labels in formulas' then you don't even need to assign the names.

    zeddy

  5. #5
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name = RowHeader_ColumnHeader (97)

    The INTERSECT operator only works for cells on the same worksheet as the calling reference.

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

    Re: Name = RowHeader_ColumnHeader (97)

    That is not entirely true.

    As long as you actually *Create* the names using Insert, name, create, the intersect operator will work with those names on any sheet.

    It is however possible to use that method on *not* defined names. Then Excel tries to resolve the names you entered by looking in the same sheet and trying to find a table that contains the name(s) you specified as its header row and/or column.
    It seems a handy method, but it is better to really name the ranges to prevent Excel start using names you've defined elsewhere or maybe used twice, which might yield unexpected results...
    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
  •