Range Selection

[Hydra]

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.


???

 

[broro183]

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

 

[Jim Cone]

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" <[email protected]>
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.
???

 

[Hydra]

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"

?????

 

[Hydra]

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.

 

[Patrick Molloy]

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]

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]

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]

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?) ;-)

 

[Hydra]

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.

 

[Hydra]

Do I need to use something like:

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


????

 

[Hydra]

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]

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]

Oh, of course. Now I get it.

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