Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named cells in an array to a column and to a row
All the code works in the macro that this excerpt is from, so I did not include the entire macro.
The .Resize(columnsize:... puts the named cells values in a row just fine. The .Resize(rowsize:... puts the first named cell value ONLY in ALL the cells in the column. So I get a column of nine values that is in cell AAAA2. What gives with this?? Thanks. Howard Set myRng = wksSource.Range("AAAA2,AAAA4,FFFF2,GGGG4,NNNN2,OOO O4,VVVV20,XXXX8,XXXX20") With wksSource wksTarget.Range("C2").Resize(columnsize:=myRng.Cel ls.Count) = myArr wksTarget.Range("B2").Resize(rowsize:=myRng.Cells. Count) = myArr End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named cells in an array to a column and to a row
Hi Howard,
Am Wed, 19 Feb 2014 11:16:30 -0800 (PST) schrieb L. Howard: With wksSource wksTarget.Range("C2").Resize(columnsize:=myRng.Cel ls.Count) = myArr wksTarget.Range("B2").Resize(rowsize:=myRng.Cells. Count) = myArr End With wksTarget.Range("B2").Resize(rowsize:=myRng.Cells. Count) _ = Worksheetfunction.Transpose(myArr) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named cells in an array to a column and to a row
To explain Claus' solution.., a 1D array will transfer to a worksheet
as '1 row x n cols' by default, and so to get it to transfer as 'n rows x 1 col' you need to transpose it. You will only get the number of elements that fit into the resize. If the resize is larger than the array then the extra cells display #N/A. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named cells in an array to a column and to a row
On Wednesday, February 19, 2014 11:40:34 AM UTC-8, GS wrote:
To explain Claus' solution.., a 1D array will transfer to a worksheet as '1 row x n cols' by default, and so to get it to transfer as 'n rows x 1 col' you need to transpose it. You will only get the number of elements that fit into the resize. If the resize is larger than the array then the extra cells display #N/A. -- Garry Thanks Claus for the snippet and thank you Garry for 'splainin it. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a column of a 2D array = named range | Excel Programming | |||
Selecting all the active cells in a named column. | Excel Worksheet Functions | |||
Moving many named cells at once to the next column | Excel Worksheet Functions | |||
DIFFERENT NAMED CELLS IN COLUMN,HOW DO I GET LAST ONE ON BOTTOM? | Excel Worksheet Functions | |||
Possible to reference column of named range in array formula? | Excel Programming |