Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
3 Posts
Discussion Starter · #1 ·
I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.
THanks
 

·
Registered
Joined
·
8,546 Posts
This should reconfigure your data to columns 2:6 ; if it's what you need, just add a line at the end to delete the first (original) column.

Sub test()
x = WorksheetFunction.CountA(Range("A:A")) / 5
For i = 1 To x
Cells(i, 2).Resize(, 5) = Application.Transpose(Cells((i - 1) * 5 + 1, 1).Resize(5))
Next i
End Sub
 

·
Registered
Joined
·
3 Posts
Discussion Starter · #3 ·
This should reconfigure your data to columns 2:6 ; if it's what you need, just add a line at the end to delete the first (original) column.

Sub test()
x = WorksheetFunction.CountA(Range("A:A")) / 5
For i = 1 To x
Cells(i, 2).Resize(, 5) = Application.Transpose(Cells((i - 1) * 5 + 1, 1).Resize(5))
Next i
End Sub
I created a new macro called test, copied your code into it, tried to run it and got a Compile Error; syntax error
 

·
Registered
Joined
·
8,546 Posts
Another way, without macros.

Assuming you have values (say, 1 to 20) in A1:A20, this formula in B1:

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

Then copy B1 to B1:F1, and B1:F1 to B1:F4.
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top