Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Headers/Footers from a named range of cells in a worksheet | Excel Discussion (Misc queries) | |||
Create list of Named Ranges | Excel Worksheet Functions | |||
Create named ranges | Excel Programming | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
Using VB to to create named ranges | Excel Programming |