Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 -- thanks as always for the help jer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
Dave, thank you for your time, talent and patience
-- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
Does that mean it worked or you just gave up?
jer wrote: Dave, thank you for your time, talent and patience -- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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 -- thanks as always for the help jer "Dave Peterson" wrote: Does that mean it worked or you just gave up? jer wrote: Dave, thank you for your time, talent and patience -- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
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.... jer wrote: 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 -- thanks as always for the help jer "Dave Peterson" wrote: Does that mean it worked or you just gave up? jer wrote: Dave, thank you for your time, talent and patience -- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
Thanks Dave and have a good night
-- thanks as always for the help jer "Dave Peterson" wrote: 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.... jer wrote: 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 -- thanks as always for the help jer "Dave Peterson" wrote: Does that mean it worked or you just gave up? jer wrote: Dave, thank you for your time, talent and patience -- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping colums and rows
Glad it worked!
jer wrote: Thanks Dave and have a good night -- thanks as always for the help jer "Dave Peterson" wrote: 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.... jer wrote: 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 -- thanks as always for the help jer "Dave Peterson" wrote: Does that mean it worked or you just gave up? jer wrote: Dave, thank you for your time, talent and patience -- thanks as always for the help jer "Dave Peterson" wrote: 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 jer wrote: 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 "Dave Peterson" wrote: 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. jer wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- thanks as always for the help jer -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rows and colums | Excel Discussion (Misc queries) | |||
How do change rows to colums AND columns to rows | Excel Discussion (Misc queries) | |||
Print few rows with many colums so that rows wrap on printed pages | Excel Discussion (Misc queries) | |||
colums and rows ... | Excel Worksheet Functions | |||
How do I name the colums and rows? | New Users to Excel |