Hi All,

I was wondering if someone can help with writing an Excel Macro or
some scripting.

What I am trying to achieve is the following.

In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers
(i.e L0123456)

What I want to be able to do is move the values from column A to
Columns B-K (10 Columns) 10 values at a time, upto 100 lines.

A B C D E F G H I J
K
L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9
L00010
L00002 L00011
L00003
L00004
..5
..6
..7
..8
..9
L00010
L00011

Any help would be appreciated.

Thanks
George

I do not follow your example.

What determines the values in cols B to K? In your example it
is not obvious what dictates the values. What you write implies
that the cols B to K should have the SAME value as col A but
your example does not support this.

When you say "10 at a time" do you mean from A1:A10 into
all the appropriate cols or do you meant A1 to B1:K1?

Chrissy.


George Plakas wrote
Hi All,

I was wondering if someone can help with writing an Excel Macro or
some scripting.

What I am trying to achieve is the following.

In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers
(i.e L0123456)

What I want to be able to do is move the values from column A to
Columns B-K (10 Columns) 10 values at a time, upto 100 lines.

A B C D E F G H I J
K
L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9
L00010
L00002 L00011
L00003
L00004
.5
.6
.7
.8
.9
L00010
L00011

Any help would be appreciated.

Thanks
George

Sorry Chrissy,

What I mean is to move from:-

Cells A1 to B1
Cells A2 to C1
Cells A3 to D1
Cells A4 to E1
Cells A5 to F1
Cells A6 to G1
Cells A7 to H1
Cells A8 to I1
Cells A9 to J1
Cells A10 to K1
Cells A11 to B2
Cells A12 to C2
Cells A13 to D2
Cells A14 to E2
Cells A15 to F2
Cells A16 to G2
Cells A17 to H2
Cells A18 to I2
Cells A19 to J2
Cells A20 to K2
Cells A21 to B3
Cells A22 to C3

ans so on.

Hope this helps.

Thanks
George


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sub MoveData()
Dim RNG As Range
Dim iCol As Integer
Dim iCols As Integer
Dim iRows As Integer
Dim iCount As Integer

iCol = 0
iCols = 10 ' Number of Cols you want to use
iRows = 1000 ' Number of Rows of Data

Set RNG = Range("A1") ' The Top Left of the Data

For iCount = 0 To iRows / iCols
For iCol = 1 To iCols
RNG.Offset(iCount, iCol) = RNG.Offset(iCount * iCols + iCol - 1, 0)
Next iCol
Next iCount
End Sub


Chrissy.

"George Plakas" wrote in message ...
Sorry Chrissy,

What I mean is to move from:-

Cells A1 to B1
Cells A2 to C1
Cells A3 to D1
Cells A4 to E1
Cells A5 to F1
Cells A6 to G1
Cells A7 to H1
Cells A8 to I1
Cells A9 to J1
Cells A10 to K1
Cells A11 to B2
Cells A12 to C2
Cells A13 to D2
Cells A14 to E2
Cells A15 to F2
Cells A16 to G2
Cells A17 to H2
Cells A18 to I2
Cells A19 to J2
Cells A20 to K2
Cells A21 to B3
Cells A22 to C3

ans so on.

Hope this helps.

Thanks
George


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,
You don't need a macro for this...

Do the following

1. Select all the cells you want to change(Copy)
2. create a new sheet
3. go to the paste special screen
4. check the "Transpose"
5. Click OK

If you don't see what you expected. Please le me know


Annamala

"Chrissy" wrote in message ...
Sub MoveData()
Dim RNG As Range
Dim iCol As Integer
Dim iCols As Integer
Dim iRows As Integer
Dim iCount As Integer

iCol = 0
iCols = 10 ' Number of Cols you want to use
iRows = 1000 ' Number of Rows of Data

Set RNG = Range("A1") ' The Top Left of the Data

For iCount = 0 To iRows / iCols
For iCol = 1 To iCols
RNG.Offset(iCount, iCol) = RNG.Offset(iCount * iCols + iCol - 1, 0)
Next iCol
Next iCount
End Sub


Chrissy.

"George Plakas" wrote in message ...
Sorry Chrissy,

What I mean is to move from:-

Cells A1 to B1
Cells A2 to C1
Cells A3 to D1
Cells A4 to E1
Cells A5 to F1
Cells A6 to G1
Cells A7 to H1
Cells A8 to I1
Cells A9 to J1
Cells A10 to K1
Cells A11 to B2
Cells A12 to C2
Cells A13 to D2
Cells A14 to E2
Cells A15 to F2
Cells A16 to G2
Cells A17 to H2
Cells A18 to I2
Cells A19 to J2
Cells A20 to K2
Cells A21 to B3
Cells A22 to C3

ans so on.

Hope this helps.

Thanks
George


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,
You don't need a macro for this...

Do the following

1. Select all the cells you want to change(Copy)
2. create a new sheet
3. go to the paste special screen
4. check the "Transpose"
5. Click OK

If you don't see what you expected. Please le me know


Annamala

What you suggest will take the 1000 cells, from cells A1:A1000
and put them into one line. That will not work as there are only
256 columns. Even if it did work, it would not give the OP what
he wanted which is a 10x100 grid of numbers from his original
list of 1000 cells.

You are correct that you do not need a macro. The way you
suggest is not the solution thought. To do it without a macro
you would enter this formula into each of the cells in the
10x100 grid where the results are needed. This requires the
original data to be in cells A1:An and the results to be in
cells B1:K(n/10) but it can be modified for any number of columns
and any destination area

=INDIRECT("A"&(ROW()-1)*10+COLUMN()-1)

Chrissy.



Annamala wrote
Hi,
You don't need a macro for this...

Do the following

1. Select all the cells you want to change(Copy)
2. create a new sheet
3. go to the paste special screen
4. check the "Transpose"
5. Click OK

If you don't see what you expected. Please le me know


Annamala

Are you trying to put all the values into a giant matrix (A1:K100) with no blank
rows so you could delete column A?

If yes, how about this routine:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim NumberOfRows As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("sheet1")
FirstRow = 1
NumberOfRows = 10
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step NumberOfRows
.Cells(iRow, "A").Resize(NumberOfRows, 1).Copy
.Cells(iRow, "B").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, Transpose:=True
Next iRow
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
.Range("a1").EntireColumn.Delete
End With

End Sub


It just goes down column A in groups of 10 rows and copies|pastespecial with a
transpose.

I started with this:

$A$1
$A$2
$A$3
$A$4
$A$5
$A$6
$A$7
$A$8
$A$9
$A$10
$A$11
$A$12
$A$13
$A$14
$A$15
$A$16
....
$A$100

And ended with this:

$A$1 $A$2 $A$3 $A$4 $A$5 $A$6 $A$7 $A$8 $A$9 $A$10
$A$11 $A$12 $A$13 $A$14 $A$15 $A$16 $A$17 $A$18 $A$19 $A$20
$A$21 $A$22 $A$23 $A$24 $A$25 $A$26 $A$27 $A$28 $A$29 $A$30
$A$31 $A$32 $A$33 $A$34 $A$35 $A$36 $A$37 $A$38 $A$39 $A$40
$A$41 $A$42 $A$43 $A$44 $A$45 $A$46 $A$47 $A$48 $A$49 $A$50
$A$51 $A$52 $A$53 $A$54 $A$55 $A$56 $A$57 $A$58 $A$59 $A$60
$A$61 $A$62 $A$63 $A$64 $A$65 $A$66 $A$67 $A$68 $A$69 $A$70
$A$71 $A$72 $A$73 $A$74 $A$75 $A$76 $A$77 $A$78 $A$79 $A$80
$A$81 $A$82 $A$83 $A$84 $A$85 $A$86 $A$87 $A$88 $A$89 $A$90
$A$91 $A$92 $A$93 $A$94 $A$95 $A$96 $A$97 $A$98 $A$99 $A$100


George Plakas wrote:

Hi All,

I was wondering if someone can help with writing an Excel Macro or
some scripting.

What I am trying to achieve is the following.

In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers
(i.e L0123456)

What I want to be able to do is move the values from column A to
Columns B-K (10 Columns) 10 values at a time, upto 100 lines.

A B C D E F G H I J
K
L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9
L00010
L00002 L00011
L00003
L00004
.5
.6
.7
.8
.9
L00010
L00011

Any help would be appreciated.

Thanks
George

--

Dave Peterson