Named cells in an array to a column and to a row

L

L. Howard

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,OOOO4,VVVV20,XXXX8,XXXX20")

With wksSource
wksTarget.Range("C2").Resize(columnsize:=myRng.Cells.Count) = myArr
wksTarget.Range("B2").Resize(rowsize:=myRng.Cells.Count) = myArr
End With
 
C

Claus Busch

Hi Howard,

Am Wed, 19 Feb 2014 11:16:30 -0800 (PST) schrieb L. Howard:
With wksSource
wksTarget.Range("C2").Resize(columnsize:=myRng.Cells.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.
 
G

GS

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
 
L

L. Howard

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.

Thanks Claus for the snippet and thank you Garry for 'splainin it.

Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top