Add Method Names Collection, assigning a relative value

[Ed S.]

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.

[Al Chara]

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

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

[Ed S.]

Does not work - Constructs incorrect Reference to:

[davers5]

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

[Al Chara]

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.

[Ed S.]

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!!!!