Moving Data

Solved/Closed
rhoknee - Mar 26, 2010 at 09:10 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 1, 2010 at 09:16 PM
Is there any one who can kindly come to my rescue!!
I have been presented with date in the form:
A
B
C
D
E
F
G
H
I
J
?
My aim is to present it the form:

A C E G I ?

B D F H J

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 26, 2010 at 11:24 PM
try this macro

Sub test()
Dim r As Range, c As Range
Dim dest As Range
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
If c.Row Mod 2 = 1 Then
c.Copy
Set dest = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
dest.PasteSpecial
End If
Next c
For Each c In r
If c.Row Mod 2 = 0 Then
c.Copy
Set dest = Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1)
dest.PasteSpecial
End If
Next c


End Sub
0
rhoknee Posts 7 Registration date Saturday March 27, 2010 Status Member Last seen May 12, 2010
Apr 1, 2010 at 03:58 PM
What if I have a column of numbers anywhere in a worksheet. Not necessarily in the A column.How can I use the macro you submitted to generate the results just one column away?
The second problem is that after running the macro the cursor jumps to cell A1. I would like it remain at the next cell after the results.

Numbers required results
A A C E ?
B B D F
C
D
C
F
?
Thanks.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 1, 2010 at 09:15 PM
take this line

Set r = Range(Range("A1"), Range("A1").End(xlDown))

r is defined as range from A1 to down

so you have type the first cell of the new column

for example you are having the data in column E
the line should be

Set r = Range(Range("E1"), Range("E1").End(xlDown))

this can also be even from E5 down in that case you write E5 in both cases above.
provided this does not come into clash with other statements

see again the statement
Set dest = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
what do you do here
take cells(1.columns.count)
that means last cell in row 1
that is IV1.

so if your range starts from E5 and not for E1 there may be problem

when you write or modify a macro put lot of msgbox so that when you debug(that is hitting F8 successively ) you will know the mistake
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 1, 2010 at 09:16 PM
I forgot to tick "get reply by email". I would like to get reply by email
0