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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Code:
Sub Foo3()
    Var1 = [b1] & [a2]
    ActiveSheet.Names.Add Name:=Var1, RefersTo:="=$A$1:$B$10"
End Sub
 

csmlse

New Member
Joined
Feb 22, 2007
Messages
23
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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.
 

csmlse

New Member
Joined
Feb 22, 2007
Messages
23
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.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

Open VBA Editor, (Alt-F11)
Make sure your cursor is in the "AssignNameRange" code.
Hit F8...
 

csmlse

New Member
Joined
Feb 22, 2007
Messages
23
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

csmlse

New Member
Joined
Feb 22, 2007
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,548
Members
417,151
Latest member
ChickenTenderer

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
Top