Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Name w/ cell content part of name (Excel 2000)

    Hi fellows,
    I want to name cells such that the content of the cells are part of the name. For example

    Cell C1 has "xyz1" typed in it, I want to name this cell as "P_xyz1"
    Cell C5 has "xyz5" typed in it, I want to name this cell as "P_xyz5"
    Cell D4 has "abc4" typed in it, I want to name this cell as "S_abc4"
    Cell D2 has "abc2" typed in it, I want to name this cell as "S_abc2"

    Please note that "P_" will be precede all the names in column C and "D_" will precede all names in column D.

    Out of curiosity, is there a way to name each individual cell in range c1:d5 (as per the above format)?
    Any help would be greatly appreciated.
    Siz

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

    Re: Cell Name w/ cell content part of name (Excel 2000)

    The VBA code below will do what you ask for all selected cells in columns C and D. If the contents of the cells result in duplicate names, only the last cell will be named.

    <pre>Public Sub NameCells()
    Dim oCell As Range
    If Intersect(Selection, Range("C")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Selection, Range("C"))
    If oCell.Value <> "" Then
    If oCell.Column = 3 Then
    oCell.Name = "P_" & oCell.Value
    Else
    oCell.Name = "S_" & oCell.Value
    End If
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Name w/ cell content part of name (Excel 2000)

    Hi Legare
    Thanks a lot.
    Your code is doing exactly what I requested, but the problem is, I made a mistake in my previous request (my apology). I would greatly appreciate if you revise the code based on the following information.
    For Column D, cell name is "S_" and corresponding cell value in Column C. It is like replacing "P_" with "S_" in column C cell names to get column D cell names.

    Thanks
    Siz

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

    Re: Cell Name w/ cell content part of name (Excel 2000)

    If you insert a column to the left of the one that needs to get the names and use this formula in those cells.

    Example: insert a column whilst at cell D1 and enter this formula in D1:

    ="S_" & C1
    Copy down.

    Now select both column D and E and choose Insert, Name, Create. Check the Left column box and hit OK. Now delete column D.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Cell Name w/ cell content part of name (Excel 2000)

    OK, Try this:

    <pre>Public Sub NameCells()
    Dim oCell As Range
    If Intersect(Selection, Range("C")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Selection, Range("C"))
    If oCell.Column = 3 Then
    If oCell.Value <> "" Then
    oCell.Name = "P_" & oCell.Value
    End If
    Else
    If oCell.Offset(0, -1).Value <> "" Then
    oCell.Name = "S_" & oCell.Offset(0, -1).Value
    End If
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Name w/ cell content part of name (Excel 2000)

    Thanks!! for big time help.
    Also, I am amazed by the slick trick showed by Jan. Thank you, I'll try to remember that.

    Siz

Posting Permissions

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