Range Selection
 

I created a bunch of named ranges by scanning the headers of a table and
creating named ranges that match the header names. Thes appear normally in
the names dialog box, with the correct ranges.

Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.


???

Range Selection
 

hi Hydra,

Can you please quote the error message?

To try & narrow it down...
Is the range on the active sheet of the active workbook?
What is the code inside the With statement?
Are there multiple sheets with the samed named ranges?

Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105711

Range Selection
 

You will improve your chances of getting a useful reply if you provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

I'm using windows xp and Excel 2003

I created the ranges using

..Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to set a
duplicate DIM statement or assignment. the other method of setting the
range name didn't seem to have that problem, and it appears to creat the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to set
a
duplicate DIM statement or assignment. the other method of setting the
range name didn't seem to have that problem, and it appears to creat the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to set
a
duplicate DIM statement or assignment. the other method of setting the
range name didn't seem to have that problem, and it appears to creat the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

I did not Dim any of my range names, since I do not necessarily know what
they will be in advance. Aren't they DIMed on the fly as I create them? As in:

---------------
For counter = 0 To ncols - 1
Rangename = "rng" & Cells(1, 1).Offset(0, counter).Value
MsgBox "Rangename = " & Rangename
rangeDescription = Cells(1, 1).Offset(0, counter).Address & ":" &
ActiveCell.Offset(nrows, counter).Address
MsgBox rangeDescription

'Set String(Rangename) = rangeDescription

ActiveSheet.Names.Add Name:=Rangename, RefersTo:=rangeDescription,
Visible:="True"
'MsgBox "Range =" & Rangename.Address

Next counter
--------------------


I gues I do not understand "Dim source as range"

Is source a variable name? Either that or I do not unerstand what you mean
by indirection.


(I get directed all the times, and sometimes re-directed. Is indirection
not getting any direction or is it deliberate misdirection?) ;-)


"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

So Itried:

Dim Rangename As Range
Set Rangename = Range(Range(rangeDescription)) '' remember this is
INDIRECTION!

With source
.Interior.Color = vbRed
.Font.Size = 18
End With

Where RangeName and RangeDescription are variables

But Dim Rangename As Range therew an error message that says "duplicate
Declaration in scope."

I suppose I could get around this by just creatig range names and then
quoting them explicitly, but it would be nice If I could tie the name to the
column heading somehow so I don't have meaningless names that I have to
remember the real meaning of.....


What is the true meaning of Columnheading, Master?
Snatch the pebble from my hand.








"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

Do I need to use something like:

With Range(""" & Rangename & """)
Do Stuff
End with


????



"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

What I finally came up with was :

ActiveCell.Range("A1:" & Lastaddress).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

But what I really need to use is something like your "end down" code to set
the range for each column individually.





"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I had
planned to put in code similar to yours. Thanks for saving me from writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because it
confused the variable with the column name and accused me of trying to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

I'm lost with where you're going with this.

simply

1)
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With

2) Range("A1").Name = "something"

3) Range("A1:G10").Name = "something"

4) Cells(1,5).Name = "something"

in (1) the name to be used is in a cell eg

5) Range("A1:G10").Name = Range("h1").Value

"Hydra" wrote in message
...
What I finally came up with was :

ActiveCell.Range("A1:" & Lastaddress).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

But what I really need to use is something like your "end down" code to
set
the range for each column individually.





"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I
had
planned to put in code similar to yours. Thanks for saving me from
writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying
to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the
rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and
column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is
copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because
it
confused the variable with the column name and accused me of trying
to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned
properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a
table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???

Range Selection
 

Oh, of course. Now I get it.



"Patrick Molloy" wrote:

I'm lost with where you're going with this.

simply

1)
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With

2) Range("A1").Name = "something"

3) Range("A1:G10").Name = "something"

4) Cells(1,5).Name = "something"

in (1) the name to be used is in a cell eg

5) Range("A1:G10").Name = Range("h1").Value

"Hydra" wrote in message
...
What I finally came up with was :

ActiveCell.Range("A1:" & Lastaddress).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

But what I really need to use is something like your "end down" code to
set
the range for each column individually.





"Patrick Molloy" wrote:

no worries.
you certainly can use the range names ...

dim source as range
set source = range(Range("D1")) '' remember this is INDIRECTION!

with source
.interior.color = vbRed
.Font.Size = 18
end with



"Hydra" wrote in message
...
For this exercise the ranges are all the same length (400 rows), but I
had
planned to put in code similar to yours. Thanks for saving me from
writing
it!!!!

Still don't get why I can't USE the ranges, having created them.

Hydra

"Patrick Molloy" wrote:

are you naming the entire column or just a bit of it

here's an example:=

Option Explicit
Sub test()
NameColumn "D"
Range(Range("D1")).Select
End Sub
Sub NameColumn(col As String)
Dim source As Range
With Range(Cells(2, col), Cells(2, col).End(xlDown))
.Name = Cells(1, col)
End With
End Sub




"Hydra" wrote in message
...
Sorry, Should say


Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange)

would not work because it

confused the variable with the column name and accused me of trying
to
set
a
duplicate DIM statement or assignment. the other method of setting
the
range name didn't seem to have that problem, and it appears to creat
the
ranges, although I cannot subsequently access them.

The ranges are not DIM ed in advance since I am reading the
rangenames
out
of th ecolumn headers, which may change.




"Hydra" wrote:

I'm using windows xp and Excel 2003

I created the ranges using

.Names.AddName:= ColumnHeader, RefersTo:= ColumnRange

where Columnheader is a verable that holds the column name and
column
range
is a variable that reads in the desired columnrange.

I could not use

ActiveSheet.Range.Name = "rngResource".Activate

Where "rngResource" is one of the range names, although this is
copied
right
out of the help screen. This throws a syntax error message.



Set ColumnHeader = Worksheets("Sheet1").Range(Columnrange) because
it
confused the variable with the column name and accused me of trying
to
set a
duplicate DIM statement or assignment.

In any case the range names appear to be there and assigned
properly.

The With Range("range") format threw and error message that said

"Method Range of Object Global Failed"

?????







"Jim Cone" wrote:

You will improve your chances of getting a useful reply if you
provide...
Your Excel version
Your operating system
The error number
The error description
The code line that causes the error
Where the code is located (which module)
What steps you have taken to fix the error
--
Jim Cone
Portland, Oregon USA



"Hydra"
wrote in message
I created a bunch of named ranges by scanning the headers of a
table
and
creating named ranges that match the header names.
Thes appear normally in the names dialog box, with the correct
ranges.
Yet when I try to access one of the ranges as in

With range("myrange")
(Attributes or code
End with

I get an error message.
???