looping colums and rows

[jer]

Some assistance please. I am working with a spreadsheet that was created
outside of excel and I am trying to create a simple spreadsheet within the
workbook on a different sheet. The first 4 rows are header and the next 4
rows are details for the respective headers

Header 1a header 1b header 1c ...
Header 2a header 2b header 2c ...
Header 3a header 3b header 3c ...
Header 4a header 4b header 4c ...
Details 1a Details 1b Header 1c
Details 2a Details 2b Header 2c
Details 3a Details 3b Header 3c
Details 4a Details 4b Header 4c
....
I am trying to convert to excel "standard" worksheet
Header 1a Header2a header3a header4a header1b ....
details 1a details2a details3a details4a details1b ...

there are 9 columns in the report. I am seriously having problems with this
I can loop through the columns but I am not sure how to go to the next row
when all the details for 1 record is transposed to the new worksheet. See my
attempt below
Sub check()
Dim larow As Long, k As Long
larow = Cells(Rows.Count, 1).End(xlUp).Row

For k = larow To 1 Step -1

If IsNumeric(Right(Cells(k, 1), 10)) Then
Dim cell As Range, cell1 As Range
Set cell = Cells(k, 1)
Set cell1 = ActiveWorkbook.Sheets("Sheet2").Cells(2, 1)
Do Until IsEmpty(cell.Value) '?? should this be for ... next??

Range(cell, cell.Offset(4, 0)).Copy
cell1.Select
Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
Set cell = cell.Offset(0, 1) 'traspose next block
Set cell1 = cell1.Offset(0, 5) 'how do I go to next row
after 'transposing first record??
Loop
End If
Next
End Sub

 

[Dave Peterson]

I'm not sure I understand the layout of the original data.

Is it:
4 headers
4 details for rec#1
4 details for rec#2
4 details for rec#3
4 details for rec#4

or do you have:
4 headers
4 details for rec#1
4 headers
4 details for rec#2
4 headers
4 details for rec#3
4 headers
4 details for rec#4

I'm guessing that it's the top version...

If that's right, then I think that this does what you want:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCtr As Long
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyHeaders As Long
Dim HowManyCols As Long

Set CurWks = Worksheets("Sheet1")

Set RptWks = Worksheets.Add

oRow = 1
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

HowManyHeaders = 4
HowManyCols = .Cells(1, Columns.Count).End(xlToLeft).Column

oRow = 1
For iRow = FirstRow To LastRow Step HowManyHeaders
For iCtr = 1 To HowManyHeaders
oCol = HowManyCols * (iCtr - 1) + 1
.Cells(iRow, "A").Resize(1, HowManyCols).Copy _
Destination:=RptWks.Cells(oRow, oCol)
Next iCtr
oRow = oRow + 1
Next iRow

End With

RptWks.UsedRange.Columns.AutoFit

End Sub


It does assume that the last row can be determined by the last used cell in
column A. And the headers all end in the same column (determined by row 1).

 

[jer]

Dave, thanks for the quick response I am sorry if I was not all that clear
In the example provided the resulting spreadsheet should have 12 columns and
2 - the header row and details under the respective rows - like

Header 1a Header 2a Header 3a Header 4a Header 1b Header 2b Header 3b etc
Details1a Details2a Details3a Details4a Details1b Details2b Details3b
3tc
your sample did not work, any other suggestions
--
thanks as always for the help
jer

I'm not sure I understand the layout of the original data.

Is it:
4 headers
4 details for rec#1
4 details for rec#2
4 details for rec#3
4 details for rec#4

or do you have:
4 headers
4 details for rec#1
4 headers
4 details for rec#2
4 headers
4 details for rec#3
4 headers
4 details for rec#4

I'm guessing that it's the top version...

If that's right, then I think that this does what you want:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCtr As Long
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyHeaders As Long
Dim HowManyCols As Long

Set CurWks = Worksheets("Sheet1")

Set RptWks = Worksheets.Add

oRow = 1
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

HowManyHeaders = 4
HowManyCols = .Cells(1, Columns.Count).End(xlToLeft).Column

oRow = 1
For iRow = FirstRow To LastRow Step HowManyHeaders
For iCtr = 1 To HowManyHeaders
oCol = HowManyCols * (iCtr - 1) + 1
.Cells(iRow, "A").Resize(1, HowManyCols).Copy _
Destination:=RptWks.Cells(oRow, oCol)
Next iCtr
oRow = oRow + 1
Next iRow

End With

RptWks.UsedRange.Columns.AutoFit

End Sub


It does assume that the last row can be determined by the last used cell in
column A. And the headers all end in the same column (determined by row 1).

 

[Dave Peterson]

I don't understand.

You have 9 columns of input data and you want 12 columns of output?

And you didnt address the layout of the input.

If all you did was change from 9 to 12 and to two headers, try changing this:

HowManyHeaders = 4

And make sure that row 1 has the correct number of headers for all the headers.

Dave, thanks for the quick response I am sorry if I was not all that clear
In the example provided the resulting spreadsheet should have 12 columns and
2 - the header row and details under the respective rows - like

Header 1a Header 2a Header 3a Header 4a Header 1b Header 2b Header 3b etc
Details1a Details2a Details3a Details4a Details1b Details2b Details3b
3tc
your sample did not work, any other suggestions

 

[jer]

thanks again Dave. Again it seems I have not made myself clear, my apologies
the original file was created outside of excel
the original file has 9 columns. The example I submitted has 3 columns
The result of the original file would be 45 columns, that is each cell in
the first 5 rows are headers. In the example I submitted it would be 15
columns (I had 12 in previous posting which was incorrect sorry for the
confusion), that is each cell in the first 5 rows are headers. Cell A6 in
the original becomes A2 in the converted sheet . My thinking was that I loop
through the columns and transpose the range(A1:A5),(B1:B5).. on a new sheet.
I was able to do this. Next step would be to transpose the data from the
original sheet onto the new sheet so that from the original sheet
range(A6:A10) becomes Range(A2:E2), B6:B10 becomes F2:J2 etc
hope this makes it clearer
--
thanks as always for the help
jer

I don't understand.

You have 9 columns of input data and you want 12 columns of output?

And you didnt address the layout of the input.

If all you did was change from 9 to 12 and to two headers, try changing this:

HowManyHeaders = 4

And make sure that row 1 has the correct number of headers for all the headers.

 

[Dave Peterson]

So each group of 5 rows is looked at one column at a time. And each of those
single columns is transposed to a single row (and extending to the right) on the
new sheet?

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet

Dim iRow As Long
Dim iCol As Long
Dim oRow As Long
Dim oCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long

Dim HowManyHeaders As Long

HowManyHeaders = 5

Set CurWks = Worksheets("Sheet1")

Set RptWks = Worksheets.Add

oRow = 1
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iRow = FirstRow To LastRow Step HowManyHeaders
For iCol = FirstCol To LastCol
oCol = (HowManyHeaders * (iCol - 1)) + 1
.Cells(iRow, iCol).Resize(HowManyHeaders, 1).Copy
RptWks.Cells(oRow, oCol).PasteSpecial Transpose:=True
Next iCol
oRow = oRow + 1
Next iRow
End With

RptWks.UsedRange.Columns.AutoFit

End Sub

thanks again Dave. Again it seems I have not made myself clear, my apologies
the original file was created outside of excel
the original file has 9 columns. The example I submitted has 3 columns
The result of the original file would be 45 columns, that is each cell in
the first 5 rows are headers. In the example I submitted it would be 15
columns (I had 12 in previous posting which was incorrect sorry for the
confusion), that is each cell in the first 5 rows are headers. Cell A6 in
the original becomes A2 in the converted sheet . My thinking was that I loop
through the columns and transpose the range(A1:A5),(B1:B5).. on a new sheet.
I was able to do this. Next step would be to transpose the data from the
original sheet onto the new sheet so that from the original sheet
range(A6:A10) becomes Range(A2:E2), B6:B10 becomes F2:J2 etc
hope this makes it clearer

 

[jer]

Dave, thank you for your time, talent and patience

 

[Dave Peterson]

Does that mean it worked or you just gave up?

Dave, thank you for your time, talent and patience

 

[jer]

Dave, Oh, it worked great, it is just what I wanted. Could you explain the
following line of code

oCol = (HowManyHeaders * (iCol - 1)) + 1

I am not sure I understand this line

 

[Dave Peterson]

oCol (output column) is the variable that represents what column gets pasted to.

And that line is in the middle of a loop that's varying the iCol (input column).

And if HowManyHeaders is equal to 5 and since iCol varies from 1 to something...

oCol = (HowManyHeaders * (iCol - 1)) + 1

First time through (icol = 1)
ocol = (5 * (1 - 1) + 1) which is 5*0 + 1 or just 1

Second time through (icol = 2)
ocol = ocol = (5 * (2 - 1) + 1) which is 5*1 + 1 or just 6

3rd time through, it'll be 11, then 16, 21....

Dave, Oh, it worked great, it is just what I wanted. Could you explain the
following line of code

oCol = (HowManyHeaders * (iCol - 1)) + 1

I am not sure I understand this line

 

[jer]

Thanks Dave and have a good night

 

[Dave Peterson]

Glad it worked!

Thanks Dave and have a good night