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

[ManhattanRebel]

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.

 

[Dick Kusleika]

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)

 

[ManhattanRebel]

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.

 

[ManhattanRebel]

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]

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.

 

[Dick Kusleika]

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.

'Red One' should not be a problem. Note that the code wraps in most
newsreaders. Make sure that's all one line.

 

[Dick Kusleika]

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.

 

[ManhattanRebel]

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.

 

[ManhattanRebel]

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

 

[ManhattanRebel]

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

 

[Dave Peterson]

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

 

[Dick Kusleika]

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

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.

 

[ManhattanRebel]

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.