Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Add Method Names Collection, assigning a relative value

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Need help finishing this code.

    I am working at developing a macro to add reference names. The trick to this one is the ReferstoR1C1: parameter is a variable that is based on active cell in the loop below.

    Correct output, that is, when the field is added, must look like:

    =WrkSheet!$C1:$C1 (this is intended to refer to only the column.

    Here is the code segent that i need help with:

    ...
    y = ActiveCell.Column
    x = ActiveCell.Row

    ' Position to last Column of WorkSheet.
    Selection.End(xlToRight).Select
    z = ActiveCell.Column

    'Select Range.
    Range(Cells(x, y), Cells(x, z)).Select

    For Each Cell In Selection
    RName = ActiveCell.Offset(1, 0).Value
    ActiveCell.Offset(0, 1).Select

    This line of code is incorrect:
    ActiveWorkbook.Names.Add Name:=RName, _
    RefersToR1C1:=????

    Next Cell



    How do I get the Active Column as a relative value for the add Name object.

    To understand my intentions
    1) open a workbook,
    2)select column B as an example
    3) Ctl F3

    Look at Refers to box.
    this is the value I need to replicate based on the column the next cell is pointing at in the loop.


    EMSS

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I didn't test it, but try the following code:

    ActiveWorkbook.Names.Add Name:=RName, RefersToR1C1:=ActiveCell.EntireColumn.Address

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does not work - Constructs incorrect Reference to:


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you use RefersToR1C1 then you what you need to produce is the name of the sheet, the letter "c" (for column) and the number of the column. Just concatenate the text strings:

    ActiveWorkbook.Names.Add Name:=RName, RefersToR1C1:=Activesheet.name & "!" & "c" & y (if that's the correct variable for the column number).

    Dave

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    ActiveWorkbook.Names.Add Name:=RName, RefersTo:="=" & ActiveCell.EntireColumn.Address

    Make sure you place it in the proper place depending on which value you want to make the named range. Verify that all your activecell offsets are correct. They seemed like they might be out of place.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you both. I have the piece of code running as needed.

    Here is the final solution, I am posting it for anyone who may need some help with this topic:

    For Each Cell In Selection
    RName = ActiveCell.Offset(1, 0).Value
    RefColumn = ActiveCell.Column
    ActiveWorkbook.Names.Add Name:=RName, _

    RefersToR1C1:="=" & ActiveSheet.Name & "!" & "C" & RefColumn

    ActiveCell.Offset(0, 1).Select
    Next Cell

    Thanks again!!!!

    EMSS

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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