I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
i = 2
Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub

Hi

I think this what your want to do:

Sub Transp()
Dim DestRow As Long, r As Long
DestRow = 2
For r = 2 To 3295 Step 9
Range(Cells(r, 2), Cells(r + 8, 2)).Copy _
Destination:=Cells(DestRow, 3)
DestRow = DestRow + 1
Next
End Sub

Regards,
Per

On 8 Maj, 15:42, Darius wrote:
I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
* *i = 2
* * * * Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub

thank you from both,

"Per Jessen" wrote:

Hi

I think this what your want to do:

Sub Transp()
Dim DestRow As Long, r As Long
DestRow = 2
For r = 2 To 3295 Step 9
Range(Cells(r, 2), Cells(r + 8, 2)).Copy _
Destination:=Cells(DestRow, 3)
DestRow = DestRow + 1
Next
End Sub

Regards,
Per

On 8 Maj, 15:42, Darius wrote:
I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
i = 2
Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub

Darius wrote:
I have data in column B from B2 to B3295 now I want to copy (Transpose) the
every 9 values in B column to one row, otherwords: b2: b10 change to c2 to k2
then B11 to B19 to c3 to k3 till the end.
I made something as below but it does not work can anybody please help.

Sub Transp()
Dim L As Long, i As Long, R As Long
R = 2
For L = 2 To 3295 Step 9
i = 2
Cells(R, i).Value = Cells(L + i, 3).Value
Next
End Sub


Put this in C2:K367

=INDIRECT("B"&(ROW()-2)*9+(COLUMN()-1))

then copy / paste special / values.