Assigning set of range names

[ADB]

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!

[Norman Jones]

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" <ADB@discussions.microsoft.com> wrote in message
news:D0BF438F-A11C-4191-B983-3E809991A034@microsoft.com...
>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!
>
>

[Norman Jones]

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" <normanjones@whereforartthou.com> wrote in message
news:e1YS%23kFwFHA.624@TK2MSFTNGP11.phx.gbl...
> 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" <ADB@discussions.microsoft.com> wrote in message
> news:D0BF438F-A11C-4191-B983-3E809991A034@microsoft.com...
>>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!
>>
>>
>
>

[ADB]

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" <ADB@discussions.microsoft.com> wrote in message
> news:D0BF438F-A11C-4191-B983-3E809991A034@microsoft.com...
> >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!
> >
> >
>
>
>

[Norman Jones]

Hi ADB,

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

---
Regards,
Norman



"ADB" <ADB@discussions.microsoft.com> wrote in message
news:2E30D220-3DCF-4C77-B9E3-28A76C16B2F3@microsoft.com...
> 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" <ADB@discussions.microsoft.com> wrote in message
>> news:D0BF438F-A11C-4191-B983-3E809991A034@microsoft.com...
>> >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!
>> >
>> >
>>
>>
>>

[ADB]

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" <ADB@discussions.microsoft.com> wrote in message
> news:2E30D220-3DCF-4C77-B9E3-28A76C16B2F3@microsoft.com...
> > 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" <ADB@discussions.microsoft.com> wrote in message
> >> news:D0BF438F-A11C-4191-B983-3E809991A034@microsoft.com...
> >> >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!
> >> >
> >> >
> >>
> >>
> >>
>
>
>