Named Ranges (Re: VBA to define named ranges)

[Geoffrey Barnard]

Named Ranges
Re: VBA to define named ranges
Hello All,

I’m using Windows XP, Excel 2003
I want to add Named Ranges from a Sheet list.
I found the following code:
Sub TEST()
‘code by HotPepper Mar 20th, 2009, 01:25 AM
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
ThisWorkbook.Names.Add Replace(c, " ", "_"), "'" & ActiveSheet.Name & "'!" & c.Offset(, 1)
Next
End Sub
I changed it to:
Sub AddNamedRangesFromList()
‘code by HotPepper Mar 20th, 2009, 01:25 AM
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
ThisWorkbook.Names.Add Replace(c, " ", "_"), "'" & c.Offset(, 1) & "'!" & c.Offset(, 2) & c.Offset(, 3)
Next
End Sub
Works great except the names are enclosed in quotation marks therefore making the names not work. I have tried to get rid of the quotation marks but I don’t know how. Also when I manually delete the quotation marks in dialog box the names do not appear in the formula bar name box unless I hit the Add button. Thanking you, Geoffrey Barnard

 

[Andrew Poulsom]

Welcome to MrExcel.

It should be:

ThisWorkbook.Names.Add Replace(c, " ", "_"), "='" & c.Offset(, 1) & "'!" & c.Offset(, 2) & c.Offset(, 3)

 

[Geoffrey Barnard]

Thanks very much Andrew. That gets rid of the quotation marks. I also added True to the end, that puts some of the named ranges in the formula bar name box. With or without 'true' the reference to the cells now enter incorrectly. e.g. Let's say the list reference is sheet1!A1 if I select A1 on the list sheet and run the macro the NamedRange is sheet1!A1. If I select A4 then run the macro the NamedRange is sheet1!A4 and all the other ranges will be offset by 3. Hope you can follow what I'm saying as I'm an amateur who does not understand a lot about VBA.

 

[Geoffrey Barnard]

Forgot to say Thanks for welcoming me.

 

[Norie]

Geoffrey

What are the named ranges you are trying to add, and what ranges do they reference?

 

[Geoffrey Barnard]

I know this has nothing to do with my question. But how do I use code tags? I shall push a few more buttons and try.
The Names may be anything at all. I've changed them several times just to see if it makes a difference. It doesn't. The ones I'm Using at the moment are generic. <table border="0" cellpadding="0" cellspacing="0" width="313"><col style="width: 54pt;" width="72"> <col style="width: 56pt;" width="75"> <col style="width: 70pt;" width="93"> <col style="width: 55pt;" width="73"> <tbody><tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt; width: 54pt;" height="14" width="72">Name</td> <td class="xl24" style="width: 56pt;" width="75">Sheet Name</td> <td class="xl24" style="width: 70pt;" width="93">Starting Range</td> <td style="width: 55pt;" width="73">Ending Range</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange1
</td> <td>Sheet4</td> <td>A1:B15</td> <td>
</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange2</td> <td>Sheet5</td> <td>A2:B16</td> <td>
</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange3</td> <td>Sheet6</td> <td>A1:B16</td> <td>
</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange4</td> <td>Sheet7</td> <td>A2:B17</td> <td>
</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange5</td> <td>Sheet8</td> <td>A1:B17</td> <td>
</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl24" style="height: 10.5pt;" height="14">MyRange6</td> <td>Sheet9</td> <td>A2:B18</td> <td>
</td> </tr> </tbody></table>

 

[Norie]

Geoffrey

Here's an alternative way to create the names.

Dim ws As Worksheet
Dim rngList As Range
Dim rngName As Range

   Set ws = Worksheets("NameList")

   Set  rngList = ws.Range("A2")

   While rngList.Value <> ""

         Sheets(rngList.Offset(, 1).Value).Range(rngList.Offset(, 2).Value).Name = rngList.Value
          Set rngList =rngList.Offset(1)
   Wend

Give that a try, if there are any problems post back.

As for the code tags, see the link in my sig.

 

[Geoffrey Barnard]

Thanks Norie, when I run the code I get Error 9 Subscript out of range. Also I cannot for the life of me understand how to work the code tags. I am going to try to find out how to do that before I post any more questions. I do not want to waste your time or any one else' for that matter. Sorry to be 'not too bright' when it comes to this stuff. I shall try to enlighten myself in how to use the forums first. Thanks to all again.

 

[Andrew Poulsom]

Your ranges need to look like this:

$A$1:$B$15

 

[Geoffrey Barnard]

Thank you very much Andrew that piece of information makes everything else fall in to place. Everything works as I require. All I have to do now is find out how to use the code boxes. Thanks to Again Everyone.