Naming Ranges

csmlse

New Member
Joined
Feb 22, 2007
Messages
23
Help....I'm a VBA novice and in over my head.

I have a spreadsheet which has four columns and numerous rows

Example:

Location - Desc - Price - ID#
Electric - Duplex Outlet - $5.00 - 12-0001

I would like to use VBA to name ranges. I need to name the Desc., Price, and ID# columns separately and want to use the Location and the headings of each column as the name range. Example - the column containing Duplex Outlet would be named ElectricDesc.

This is what I came up with, but need to figure out how to make "add name" a formula combining Location+Desc. I tried concatenate (B1,A2) but it did not work.

ActiveSheet.Names.Add Name:="MyRange1", RefersTo:="=$A$1:$B$10"

Any suggestions?

Thanks.

Laurie
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Code:
Sub Foo3()
    Var1 = [b1] & [a2]
    ActiveSheet.Names.Add Name:=Var1, RefersTo:="=$A$1:$B$10"
End Sub
 
Upvote 0
I ran into a glitch. I tried expanding the code and the last line is not working. Any suggestions?

Thanks again.

Sub AssignNameRange()
Var1 = [b2] & [c1]
ActiveSheet.Names.Add Name:=Var1, RefersTo:="=$c$1:$c$36"
Var2 = [b2] & [d1]
ActiveSheet.Names.Add Name:=Var2, RefersTo:="=$d$1:$d$36"
Var3 = [b2] & [e1]
ActiveSheet.Names.Add Name:=Var3, RefersTo:="=$e$1:$e$36"
End Sub
 
Upvote 0
Have you stepped throught the code and checked your variables?

In your code in VBA, hit F8.
A yellow highlight will show which line of code will perform next. As you tap F8 you will step through the code one line at a time.
During this process, when you move your cursor over any variables, it will display what that variable currently is. What does it show for Var3? Is it different than Var1 and Var2? Is it what you expect?

You might try changing the shortcut versions of [b2] and [e1] to Range("B2").Value and Range("E1").Value to assure they are picking up the value of the referenced cells and not text.
 
Upvote 0
I have Excel 2003 and the f8 did not work as anticipated. I've rechecked the code and unless I'm being dense today, I don't see what the problem is with the last line.
 
Upvote 0
Open VBA Editor, (Alt-F11)
Make sure your cursor is in the "AssignNameRange" code.
Hit F8...
 
Upvote 0
John,

I've just gotten back to this project in the last day and have entered all the data. I am receiving an error on the line for Var10. I'm not seeing anything obvious. Do you have a second to check it out?

Var9 = [b50] & [e1]
ActiveSheet.Names.Add Name:=Var9, RefersTo:="=$e$50:$e$63"
Var10 = Range("b83") & Range("c1")
ActiveSheet.Names.Add Name:=Var10, RefersTo:="=$c$83:$c$102"

Thanks so much for your help. If needed I can email you the actual spreadsheet if that will help.

Laurie
 
Upvote 0
What values do you have in Cells B83 and C1?
I put "The" in B83 and "End" in C1 and got a named range of "TheEnd" assigned to cells "C83:C102".
Do you have any spaces in your source cells? Named ranges have to be one word with no spaces.
 
Upvote 0
B83 - LRLights
C1 - DESCRIPTION

I just checked for extra spaces in the row's I'm having errors on and that was it. Extra spaces at the end of the word.

You saved me tons of frustration (again).

Thank you. Have a nice evening.

Laurie
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
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