Add Method Names Collection, assigning a relative value

Ed S.

Board Regular
Joined
Mar 26, 2002
Messages
90
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I didn't test it, but try the following code:

ActiveWorkbook.Names.Add Name:=RName, RefersToR1C1:=ActiveCell.EntireColumn.Address
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!!!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top