Naming Ranges using VBA

[brady911]

I have a database that will be constantly changing in length and for purposes of conditional data validation, I want to name hundreds, potentially thousands of ranges. I have made it far enough to get a table on a separate sheet with what I want the name of each range to be in column A and the actual range I want named in column B (see below for example - my current list has 500+ groups). I think I can manage a loop to run through the spreadsheet but I cannot figure out how to get it to name range D3:D4 as AAAA without manually typing it in. As I mentioned, I have 500+ ranges that will be changing in length monthly so this will not be a viable option. PLEASE HELP!


[TABLE="width: 500"]
<tbody>[TR]
[TD]AAAA[/TD]
[TD]'Vistaarpaste'!D3:D4[/TD]
[/TR]
[TR]
[TD]AAAB[/TD]
[TD]'Vistaarpaste'!D5:D8[/TD]
[/TR]
[TR]
[TD]AAAC[/TD]
[TD]'Vistaarpaste'!D9:D9
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

[TABLE="width: 500"]
<tbody>[TR]
[TD]AAAA[/TD]
[TD]'Vistaarpaste'!D3:D4[/TD]
[/TR]
[TR]
[TD]AAAB[/TD]
[TD]'Vistaarpaste'!D5:D8[/TD]
[/TR]
[TR]
[TD]AAAC[/TD]
[TD]'Vistaarpaste'!D9:D9
[/TD]
[/TR]
</tbody>[/TABLE]

Are the cells you have the range addresses in formatted as Text or as General?

 

[brady911]

They are currently General but I could convert it to text if needed.

 

[Rick Rothstein]

They are currently General but I could convert it to text if needed.

The fact that it is in a General is a possible problem and I cannot be sure simply converting the cell to Text will get around it... it kind of depends how the text ended up in the General cell originally. Here is the problem... if you type a text into a cell that starts with an apostrophe, Excel uses that leading apostrophe as a "meta-character" meaning it stops becoming a text character in the cell and becomes a signal to Excel that what follows it is text. Even if the leading apostrophe is visible (meaning it is still a text character), clicking in the Formula Bar and then exiting it by either selecting a different cell, hitting the Tab key or hitting the Enter key will instantly convert the leading apostrophe to a meta-character and once that has happened, it is a royal pain to try and remove the meta-character. I cannot be sure from here whether that leading apostrophe is still a text character or has become a meta-character. If you can still see the leading apostrophe after you convert the cells to Text, then I think all should be fine and the following macro should work for you... if not, then you will need to find a way to get the addresses into those cells after the cells have been formatted as text.

Sub NameRanges()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  For R = 1 To LastRow
    Range(Cells(R, "B").Value).Name = Cells(R, "A").Value
  Next
End Sub

 

[brady911]

This code looks like it would do exactly what I'm looking for (and with much fewer lines than I was attempting) but when I run it I get a error: Run-time error '1004': Method 'Range' of object '_Global' failed.

When I convert column B to text, the leading apostrophes do not go away.

 

[TotallyConfused]

This code looks like it would do exactly what I'm looking for (and with much fewer lines than I was attempting) but when I run it I get a error: Run-time error '1004': Method 'Range' of object '_Global' failed.

When I convert column B to text, the leading apostrophes do not go away.

Hello

I am getting the same error message. I formatted 6 cells, 3 in column A and 3 in column B as TEXT, even BEFORE I typed in some sample data. The error seems to be in the line of:

    Range(Cells(R,”B”).Value.Name = Cells(R,”A”).Value

TotallyConfused

 

[Rick Rothstein]

Hello

I am getting the same error message. I formatted 6 cells, 3 in column A and 3 in column B as TEXT, even BEFORE I typed in some sample data. The error seems to be in the line of:

    Range(Cells(R,”B”).Value).Name = Cells(R,”A”).Value

TotallyConfused

You missed the red parenthesis that I inserted above. Also, your quote marks are not the correct ones... yours are ASCII code 148 whereas the VB editor requires "normal" quote marks (ASCII code 34).

Just so you and Brady know, once I put the range names into cells formatted as Text beforehand, my code worked perfectly for naming those ranges with the names from Column A. If the code is not working, the problem almost surely will be related to that leading apostrophe.

 

[brady911]

Thanks for all your help Rick! I still wasn't able to make what I had work but I was able to move my list to the tab with ranges to remove the 'Vistaarpaste'! portion of it as that seemed to be the major issue here. Using only the cell range like below I was able to use my formulas with your code and name 500+ ranges.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]AAAA[/TD]
[TD]D3:D4[/TD]
[/TR]
[TR]
[TD]AAAB[/TD]
[TD]D5:D8[/TD]
[/TR]
[TR]
[TD]AAAC[/TD]
[TD]D9:D9[/TD]
[/TR]
</tbody>[/TABLE]