Create Named Ranges (Headers) - then using range name in formula
 

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.

Create Named Ranges (Headers) - then using range name in formula
 

On Fri, 1 Aug 2008 10:47:00 -0700, ManhattanRebel
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formu
 

On Fri, 1 Aug 2008 13:44:03 -0700, 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.


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

Create Named Ranges (Headers) - then using range name in formu
 

On Fri, 1 Aug 2008 16:09:01 -0700, ManhattanRebel
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formu
 

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
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

Create Named Ranges (Headers) - then using range name in formula
 

You're using Red as a range name, too?

with activesheet 'I like to qualify my ranges
.Range(.Cells(i, "CC"), .Cells(i, "CD")).Cut _
Destination:=.Cells(i, .range("Red").column)
end with

=========
You could be able to do this without using range names.

Dim FoundCell as range
with activesheet
with .rows(1) 'those headers are in row 1???
set foundcell = .cells.find(What:="red", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlwhole, _
SearchOrder:=xlBycolumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'what should happen
exit sub '??? with a msgbox???
end if

.Range(.Cells(i, "CC"), .Cells(i, "CD")).Cut _
Destination:=.Cells(i, foundcell.column)

end with

ManhattanRebel 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?

Thank you.

--

Dave Peterson

Create Named Ranges (Headers) - then using range name in formu
 

On Fri, 1 Aug 2008 18:55:00 -0700, ManhattanRebel
wrote:

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


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.
--

Run it again and click Debug when you get the error. In debug mode, go to
the Immediate Window (Ctl+G) and type:

?sh.Cells(i, "CC").Resize(,2).Address

?i

?sh.Range("Red One").Address

?Intersect(sh.Rows(i), sh.Range("Red One").EntireColumn).Address

Then repost your code as you have it now and the result for those four
statements. One of the will likely give you the error, but run them all
anyway.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Create Named Ranges (Headers) - then using range name in formu
 

That's basically what I tried the first time I ran it, but I forgot to put
in the last "range" command before ("Red").column).
Still get the stupid Global' error for some reason. I eventually intended
to run it the way you show without range names. Thanks.


"Dave Peterson" wrote:

You're using Red as a range name, too?

with activesheet 'I like to qualify my ranges
.Range(.Cells(i, "CC"), .Cells(i, "CD")).Cut _
Destination:=.Cells(i, .range("Red").column)
end with

=========
You could be able to do this without using range names.

Dim FoundCell as range
with activesheet
with .rows(1) 'those headers are in row 1???
set foundcell = .cells.find(What:="red", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlwhole, _
SearchOrder:=xlBycolumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'what should happen
exit sub '??? with a msgbox???
end if

.Range(.Cells(i, "CC"), .Cells(i, "CD")).Cut _
Destination:=.Cells(i, foundcell.column)

end with

ManhattanRebel 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?

Thank you.

--

Dave Peterson