In a macro, I am selecting ten columns and creating range names based on the
top row.

Then I cut cells from another column and paste it to the appropriate column
by range name.

For example, I find the word "red" in cell D95. I want to paste that word,
and the cell next to it, in the column with the range name "red" in the same
row, 95. (I just want to move it over so it's in the column with the same
name.)

If I use the following code, I get an error:
Range(Cells(i, "CC"), Cells(i, "CD")).Cut Destination:=Cells(i, "Red")

Is there a way for me to specify that "Red" is only the column part of the
cell address?

Thank you.

On Fri, 1 Aug 2008 10:47:00 -0700, ManhattanRebel
<(E-Mail Removed)> wrote:

>
>In a macro, I am selecting ten columns and creating range names based on the
>top row.
>
>Then I cut cells from another column and paste it to the appropriate column
>by range name.
>
>For example, I find the word "red" in cell D95. I want to paste that word,
>and the cell next to it, in the column with the range name "red" in the same
>row, 95. (I just want to move it over so it's in the column with the same
>name.)
>
>If I use the following code, I get an error:
>Range(Cells(i, "CC"), Cells(i, "CD")).Cut Destination:=Cells(i, "Red")
>
>Is there a way for me to specify that "Red" is only the column part of the
>cell address?
>

Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
Range("Red").EntireColumn)
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Thank you, Dick.

I think that will work fine, but I am getting a compile error "Expected:
list separator or )" The only thing I am doing different is that my range
name is not just one word. It is actually "Red One". It has a space in it.
I don't know if that messes up anything, but I suspect not.

Am I missing a ) or : somewhere?
Thx again.

"Dick Kusleika" wrote:

> On Fri, 1 Aug 2008 10:47:00 -0700, ManhattanRebel
> <(E-Mail Removed)> wrote:
>
> >
> >In a macro, I am selecting ten columns and creating range names based on the
> >top row.
> >
> >Then I cut cells from another column and paste it to the appropriate column
> >by range name.
> >
> >For example, I find the word "red" in cell D95. I want to paste that word,
> >and the cell next to it, in the column with the range name "red" in the same
> >row, 95. (I just want to move it over so it's in the column with the same
> >name.)
> >
> >If I use the following code, I get an error:
> >Range(Cells(i, "CC"), Cells(i, "CD")).Cut Destination:=Cells(i, "Red")
> >
> >Is there a way for me to specify that "Red" is only the column part of the
> >cell address?
> >
>
> Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
> Range("Red").EntireColumn)
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
>

No worry. I think I have it now. A comma needed to be inserted after
Range(Cells(i,"CC")
One comma was not enough.
Thanks again.


"ManhattanRebel" wrote:

> Thank you, Dick.
>
> I think that will work fine, but I am getting a compile error "Expected:
> list separator or )" The only thing I am doing different is that my range
> name is not just one word. It is actually "Red One". It has a space in it.
> I don't know if that messes up anything, but I suspect not.
>
> Am I missing a ) or : somewhere?
> Thx again.
>
> "Dick Kusleika" wrote:
>
> > On Fri, 1 Aug 2008 10:47:00 -0700, ManhattanRebel
> > <(E-Mail Removed)> wrote:
> >
> > >
> > >In a macro, I am selecting ten columns and creating range names based on the
> > >top row.
> > >
> > >Then I cut cells from another column and paste it to the appropriate column
> > >by range name.
> > >
> > >For example, I find the word "red" in cell D95. I want to paste that word,
> > >and the cell next to it, in the column with the range name "red" in the same
> > >row, 95. (I just want to move it over so it's in the column with the same
> > >name.)
> > >
> > >If I use the following code, I get an error:
> > >Range(Cells(i, "CC"), Cells(i, "CD")).Cut Destination:=Cells(i, "Red")
> > >
> > >Is there a way for me to specify that "Red" is only the column part of the
> > >cell address?
> > >
> >
> > Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
> > Range("Red").EntireColumn)
> > --
> > Dick Kusleika
> > Microsoft MVP-Excel
> > http://www.dailydoseofexcel.com
> >

I am getting a "Range of Object'_Global' error 1004 when I use this formula:
Range(Cells(i, "CC")).Resize(, 2).Cut Destination:=Intersect(Rows(i),
Range("Red One").EntireColumn)

Do I have to specify which worksheet I'm using for the destination? The
sheet is "Color". Where and how should I specify that, if necessary?
Thx.



"ManhattanRebel" wrote:

> No worry. I think I have it now. A comma needed to be inserted after
> Range(Cells(i,"CC")
> One comma was not enough.
> Thanks again.
>
>
> "ManhattanRebel" wrote:
>
> > Thank you, Dick.
> >
> > I think that will work fine, but I am getting a compile error "Expected:
> > list separator or )" The only thing I am doing different is that my range
> > name is not just one word. It is actually "Red One". It has a space in it.
> > I don't know if that messes up anything, but I suspect not.
> >
> > Am I missing a ) or : somewhere?
> > Thx again.
> >
> > "Dick Kusleika" wrote:
> >
> > > On Fri, 1 Aug 2008 10:47:00 -0700, ManhattanRebel
> > > <(E-Mail Removed)> wrote:
> > >
> > > >
> > > >In a macro, I am selecting ten columns and creating range names based on the
> > > >top row.
> > > >
> > > >Then I cut cells from another column and paste it to the appropriate column
> > > >by range name.
> > > >
> > > >For example, I find the word "red" in cell D95. I want to paste that word,
> > > >and the cell next to it, in the column with the range name "red" in the same
> > > >row, 95. (I just want to move it over so it's in the column with the same
> > > >name.)
> > > >
> > > >If I use the following code, I get an error:
> > > >Range(Cells(i, "CC"), Cells(i, "CD")).Cut Destination:=Cells(i, "Red")
> > > >
> > > >Is there a way for me to specify that "Red" is only the column part of the
> > > >cell address?
> > > >
> > >
> > > Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
> > > Range("Red").EntireColumn)
> > > --
> > > Dick Kusleika
> > > Microsoft MVP-Excel
> > > http://www.dailydoseofexcel.com
> > >

On Fri, 1 Aug 2008 13:44:03 -0700, ManhattanRebel
<(E-Mail Removed)> wrote:

>Thank you, Dick.
>
>I think that will work fine, but I am getting a compile error "Expected:
>list separator or )" The only thing I am doing different is that my range
>name is not just one word. It is actually "Red One". It has a space in it.
>I don't know if that messes up anything, but I suspect not.
>
>Am I missing a ) or : somewhere?
>Thx again.
>
>>
>> Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
>> Range("Red").EntireColumn)

'Red One' should not be a problem. Note that the code wraps in most
newsreaders. Make sure that's all one line.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Fri, 1 Aug 2008 16:09:01 -0700, ManhattanRebel
<(E-Mail Removed)> wrote:

>I am getting a "Range of Object'_Global' error 1004 when I use this formula:
>Range(Cells(i, "CC")).Resize(, 2).Cut Destination:=Intersect(Rows(i),
>Range("Red One").EntireColumn)
>
>Do I have to specify which worksheet I'm using for the destination? The
>sheet is "Color". Where and how should I specify that, if necessary?
>Thx.
>

You don't *have* to, but you should. If you don't, it assumes the active
sheet. I assumed both the copied cells and the destination are on the same
sheet.

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Color")

sh.Cells(i,"CC").Resize(,2).Cut Intersect(sh.Rows(i), sh.Range("Red
One").EntireColumn)

Make sure that last bit is all on one line.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Everything is on the same worksheet. Still get the Method 'Range of Object'
_Global' failed error, even after doing what you suggested. Don't know
what's going on.


"Dick Kusleika" wrote:

> On Fri, 1 Aug 2008 16:09:01 -0700, ManhattanRebel
> <(E-Mail Removed)> wrote:
>
> >I am getting a "Range of Object'_Global' error 1004 when I use this formula:
> >Range(Cells(i, "CC")).Resize(, 2).Cut Destination:=Intersect(Rows(i),
> >Range("Red One").EntireColumn)
> >
> >Do I have to specify which worksheet I'm using for the destination? The
> >sheet is "Color". Where and how should I specify that, if necessary?
> >Thx.
> >
>
> You don't *have* to, but you should. If you don't, it assumes the active
> sheet. I assumed both the copied cells and the destination are on the same
> sheet.
>
> Dim sh As Worksheet
>
> Set sh = ThisWorkbook.Sheets("Color")
>
> sh.Cells(i,"CC").Resize(,2).Cut Intersect(sh.Rows(i), sh.Range("Red
> One").EntireColumn)
>
> Make sure that last bit is all on one line.
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
>

Excuse me. Now I'm getting the error, but instead of Global', I get
Worksheet'.

"Dick Kusleika" wrote:

> On Fri, 1 Aug 2008 13:44:03 -0700, ManhattanRebel
> <(E-Mail Removed)> wrote:
>
> >Thank you, Dick.
> >
> >I think that will work fine, but I am getting a compile error "Expected:
> >list separator or )" The only thing I am doing different is that my range
> >name is not just one word. It is actually "Red One". It has a space in it.
> >I don't know if that messes up anything, but I suspect not.
> >
> >Am I missing a ) or : somewhere?
> >Thx again.
> >
> >>
> >> Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
> >> Range("Red").EntireColumn)
>
> 'Red One' should not be a problem. Note that the code wraps in most
> newsreaders. Make sure that's all one line.
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
>

This is the code that creates name ranges from the headings:
Columns("CW:CW").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
False
Range("CE5").Select


"Dick Kusleika" wrote:

> On Fri, 1 Aug 2008 13:44:03 -0700, ManhattanRebel
> <(E-Mail Removed)> wrote:
>
> >Thank you, Dick.
> >
> >I think that will work fine, but I am getting a compile error "Expected:
> >list separator or )" The only thing I am doing different is that my range
> >name is not just one word. It is actually "Red One". It has a space in it.
> >I don't know if that messes up anything, but I suspect not.
> >
> >Am I missing a ) or : somewhere?
> >Thx again.
> >
> >>
> >> Cells(i, "CC").Resize(,2).Cut Destination:=Intersect(Rows(i),
> >> Range("Red").EntireColumn)
>
> 'Red One' should not be a problem. Note that the code wraps in most
> newsreaders. Make sure that's all one line.
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
>