Assigning set of range names
 

I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns 2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some kind
of mismatch of variable types. But this is my first excusrion into VBA and I
am floundering. Any help appreciated!

Assigning set of range names
 

Hi ADB,

Your code worked for me.

What error message are you getting?

Incidentally, I think that you could name the ten ranges with:

Sub Tester
Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False
End Sub

---
Regards,
Norman



"ADB" wrote in message
...
I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of
cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns 2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some kind
of mismatch of variable types. But this is my first excusrion into VBA and
I
am floundering. Any help appreciated!

Assigning set of range names
 

Hi ADB.

Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False

Should be:

Range("A1").Resize(10, 10).CreateNames Top:=True, _
Left:=False, Bottom:=False, Right:=False

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi ADB,

Your code worked for me.

What error message are you getting?

Incidentally, I think that you could name the ten ranges with:

Sub Tester
Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False
End Sub

---
Regards,
Norman



"ADB" wrote in message
...
I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of
cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns
2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some
kind
of mismatch of variable types. But this is my first excusrion into VBA
and I
am floundering. Any help appreciated!

Assigning set of range names
 

Hi Norman,

Thanks for the reply. The code (I repasted it into the VBA module, from
this post to make sure it is as written) gives:

Run time error `1004':

Application-defined or object-defined error.

While I can shift over to the alternative code you provide (I sort of
understand it...)it will help me move up the learning curve if I can
understand what is wrong with what I have written.

Your help is appreciated!

ADB
ADB

"Norman Jones" wrote:

Hi ADB,

Your code worked for me.

What error message are you getting?

Incidentally, I think that you could name the ten ranges with:

Sub Tester
Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False
End Sub

---
Regards,
Norman



"ADB" wrote in message
...
I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of
cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns 2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some kind
of mismatch of variable types. But this is my first excusrion into VBA and
I
am floundering. Any help appreciated!

Assigning set of range names
 

Hi ADB,

I could replicate your encountered error if any of the A1:J1 header range
included blank cells.

---
Regards,
Norman



"ADB" wrote in message
...
Hi Norman,

Thanks for the reply. The code (I repasted it into the VBA module, from
this post to make sure it is as written) gives:

Run time error `1004':

Application-defined or object-defined error.

While I can shift over to the alternative code you provide (I sort of
understand it...)it will help me move up the learning curve if I can
understand what is wrong with what I have written.

Your help is appreciated!

ADB
ADB

"Norman Jones" wrote:

Hi ADB,

Your code worked for me.

What error message are you getting?

Incidentally, I think that you could name the ten ranges with:

Sub Tester
Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False
End Sub

---
Regards,
Norman



"ADB" wrote in message
...
I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of
cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns
2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some
kind
of mismatch of variable types. But this is my first excusrion into VBA
and
I
am floundering. Any help appreciated!

Assigning set of range names
 

Hi Norman,

Ah; that's it. Some of the columns were indeed blanks ---supposed to be
placeholders for future entries. And I guess `blank' is different from " ".
So the errror message is the equivalent of an `unassigned variable' message
I've frequently seen in `other' languages.

So I need to cut off the assignment to exclude the empty columns. I reckon I
can do this.

Thank you for your help.

ADB
"Norman Jones" wrote:

Hi ADB,

I could replicate your encountered error if any of the A1:J1 header range
included blank cells.

---
Regards,
Norman



"ADB" wrote in message
...
Hi Norman,

Thanks for the reply. The code (I repasted it into the VBA module, from
this post to make sure it is as written) gives:

Run time error `1004':

Application-defined or object-defined error.

While I can shift over to the alternative code you provide (I sort of
understand it...)it will help me move up the learning curve if I can
understand what is wrong with what I have written.

Your help is appreciated!

ADB
ADB

"Norman Jones" wrote:

Hi ADB,

Your code worked for me.

What error message are you getting?

Incidentally, I think that you could name the ten ranges with:

Sub Tester
Range("A1").Resize(10, 10).CreateNames Top:=True, Left:=False
End Sub

---
Regards,
Norman



"ADB" wrote in message
...
I have a set of data arrranged in a 10x10 array.

I want to use the contents of cell (1,1) as the name for the range of
cells
(2,1), (3,1)..(10,1); and perform a similar operation on columns
2,3...10.

I wrote the code:

Sub assignames()
Dim category As String
For i = 1 To 10
category = Cells(1, i)
Range(Cells(2, i), Cells(10, i)).Name = category
Next i
End Sub

The debugger faults me on the line "Range....=". I guess this is some
kind
of mismatch of variable types. But this is my first excusrion into VBA
and
I
am floundering. Any help appreciated!