Dividing the Items of an Array over multiple Columns

[Jonathan78]

Hi all,

IMG1.jpg

I'm trying to divide the items of an array over 3 columns
Like in the image above.

I've tried the following but without succes

Please Login or Register  to view this content.

[Doc.AElstein]

Hi again Jonathan78
Using exactly the ideas ( and a lot of the code ! ) that I showed you here yesterday:
http://www.excelforum.com/showthread...t=#post4381996

This code is much simpler version of that. .....!....

Please Login or Register  to view this content.

It will take for example this

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I	J
1	1	Area 1	Area 59	6	1	1	any	3	Finks

pgcArraysSplitToColumn

And it will give you this

Row\Col	A	B	C
40	1	Area 1	Area 59
41	6	1	1
42	any	3	Finks

And here a simplified code version ( Note to run it you still need the Function shgMathsVBASHimpfGlified copied to the same module as the Main code )

Please Login or Register  to view this content.

Alan


Jack ....done it nice ??

[Bernie Deitrick]

Please Login or Register  to view this content.

[Jonathan78]

Exactly what I was looking for, Thanks Bernie!

[Jonathan78]

Thanks Doc.AElstein, but I go for the shorter solution

[Kenneth Hobson]

Since I finished this after the other posts, here it is for what it is worth.

Keep in mind that simple or short solutions are not always optimized for speed. Settings like ScreenUpdating, Calculation, Events, and such can be used to help with speed issues. My Speedup routines: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

I find that writing to an array and then writing to a range all at once is faster than one cell at a time. For your scenario, speed should not be a significant issue.

Please Login or Register  to view this content.

[Doc.AElstein]

Thanks Kenneth Hobson and Bernie Deitrick for adding here

Alan

_..

And here just a couple of versions of my code that do not need the Column Letter Function ( Function to get the Column Letter from Column Number )

The original Function is simply included in the main code line in the first code.

Please Login or Register  to view this content.

_................................


For the second a simple Function to get the Column Letter from Column Number is incorporated into the main Code

Please Login or Register  to view this content.

As before for thes examples, this is the Input Range.
Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I	J
1	1	Area 1	Area 59	6	1	1	any	3	Finks

Output is
Using Excel 2007

Row\Col	A	B	C
40	1	Area 1	Area 59
41	6	1	1
42	any	3	Finks

_..........................................................

So Last column is ( lClm ) is 9
Wanted Width ( Widf ) is 3
Wanted Top left is A40