| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Creating a table within a table
I am using Excel 2007.
I have 5 columns as illustrated below - (Table Description has no rows yet) CTwt. Size Metal Jewelers cost Table Description 5 10 Tungsten 31 4 10.5 Tungsten 35 2 11 Tungsten 50 10 11.5 Tungsten 20 8 12 Tungsten 15 5 12.5 Tungsten 50 6 13 Tungsten 45 What I need to accomplish: Column 5(Table Description) needs to be a table in each row. For example Column 5, Row 1: (new table with 2 columns and 4 rows) like this: Ctwt. 5 Size 10 Metal Tungsten Jewelers Cost 31 I need to do this with hundreds of rows. After this I will somehow convert the table into html format and upload the excel file to my webpage.... Any ideas? Thank you. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Creating a table within a table
Hi,
not quiet sure what you need, did you try to select the range, copy, paste special, transpose "urlocaljeweler" wrote: I am using Excel 2007. I have 5 columns as illustrated below - (Table Description has no rows yet) CTwt. Size Metal Jewelers cost Table Description 5 10 Tungsten 31 4 10.5 Tungsten 35 2 11 Tungsten 50 10 11.5 Tungsten 20 8 12 Tungsten 15 5 12.5 Tungsten 50 6 13 Tungsten 45 What I need to accomplish: Column 5(Table Description) needs to be a table in each row. For example Column 5, Row 1: (new table with 2 columns and 4 rows) like this: Ctwt. 5 Size 10 Metal Tungsten Jewelers Cost 31 I need to do this with hundreds of rows. After this I will somehow convert the table into html format and upload the excel file to my webpage.... Any ideas? Thank you. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Creating a table within a table
I'm not sure which way you really want to go with:
1) one row with the initial entries along with 3 additional rows to hold the added information in separate cells before beginning the next existing group, or 2) one row for everything, with the combined description text in a single cell on the same row. So I have written up code that will let you decide for yourself. The "IntoNewRows()" routine will take care of option 1, and the "IntoOneCell()" routine takes care of the second option. The "RemoveAddedRows()" routine can be used to delete the added rows inserted if you use the 1st method. To put the code into your workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In the VB Editor use Insert -- Module and then copy the code below and paste it into the code module presented to you. You can modify things like the column width I set for column E and the number of blanks in the various entries to try to get the layout out you want. To run the code, go to the [Developer] tab and click the "Macros" icon and choose the macro to run. If you do not see the [Developer] tab, use: Office Button -- [Excel Options] and in the Popular group, choose to "Show Developer tab in the Ribbon". Sub IntoOneCell() Dim initialRange As Range Dim anyCell As Range Dim LC As Long Dim theDescription As String RemoveAddedRows ' cleanup just in case Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False Columns("E:E").ColumnWidth = 20 For Each anyCell In initialRange If Not IsEmpty(anyCell) Then 'start a group theDescription = _ Range("A1") & String(14, " ") & anyCell & vbLf theDescription = theDescription & _ Range("B1") & String(14, " ") & anyCell.Offset(0, 1) & vbLf theDescription = theDescription & _ Range("C1") & String(2, " ") & anyCell.Offset(0, 2) & vbLf theDescription = theDescription & _ Range("D1") & String(2, " ") & anyCell.Offset(0, 3) anyCell.Offset(0, 4) = theDescription End If Next Set initialRange = Nothing End Sub Sub IntoNewRows() Dim initialRange As Range Dim anyCell As Range Dim LC As Long RemoveAddedRows ' cleanup just in case Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False For LC = initialRange.Cells.Count To 2 Step -1 initialRange.Cells(LC, 1).EntireRow.Insert initialRange.Cells(LC, 1).EntireRow.Insert initialRange.Cells(LC, 1).EntireRow.Insert Next Set initialRange = Nothing 'get new range Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Columns("E:E").ColumnWidth = 20 For Each anyCell In initialRange If Not IsEmpty(anyCell) Then 'start a group anyCell.Offset(0, 4) = Range("A1") & String(9, " ") & anyCell anyCell.Offset(1, 4) = Range("B1") & String(9, " ") & anyCell.Offset(0, 1) anyCell.Offset(2, 4) = Range("C1") & String(9, " ") & anyCell.Offset(0, 2) anyCell.Offset(3, 4) = Range("D1") & String(2, " ") & anyCell.Offset(0, 3) End If Next Set initialRange = Nothing End Sub Sub RemoveAddedRows() Dim initialRange As Range Dim anyCell As Range Dim LC As Long Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False For LC = initialRange.Cells.Count To 2 Step -1 If IsEmpty(initialRange.Cells(LC, 1)) Then initialRange.Cells(LC, 1).EntireRow.Delete End If Next Set initialRange = Nothing End Sub "urlocaljeweler" wrote: I am using Excel 2007. I have 5 columns as illustrated below - (Table Description has no rows yet) CTwt. Size Metal Jewelers cost Table Description 5 10 Tungsten 31 4 10.5 Tungsten 35 2 11 Tungsten 50 10 11.5 Tungsten 20 8 12 Tungsten 15 5 12.5 Tungsten 50 6 13 Tungsten 45 What I need to accomplish: Column 5(Table Description) needs to be a table in each row. For example Column 5, Row 1: (new table with 2 columns and 4 rows) like this: Ctwt. 5 Size 10 Metal Tungsten Jewelers Cost 31 I need to do this with hundreds of rows. After this I will somehow convert the table into html format and upload the excel file to my webpage.... Any ideas? Thank you. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Creating a table within a table
Thought that was a marvellous, super response. My hunch is that your Sub
IntoOneCell() does exactly what OP wanted, albeit s/he has yet to reply. Thanks for sharing your subs with us! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Creating a table within a table
I apologize! That code works great! Now all I have to do is figure out how
to convert that cell into html! Thank you very much! "JLatham" wrote: I'm not sure which way you really want to go with: 1) one row with the initial entries along with 3 additional rows to hold the added information in separate cells before beginning the next existing group, or 2) one row for everything, with the combined description text in a single cell on the same row. So I have written up code that will let you decide for yourself. The "IntoNewRows()" routine will take care of option 1, and the "IntoOneCell()" routine takes care of the second option. The "RemoveAddedRows()" routine can be used to delete the added rows inserted if you use the 1st method. To put the code into your workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In the VB Editor use Insert -- Module and then copy the code below and paste it into the code module presented to you. You can modify things like the column width I set for column E and the number of blanks in the various entries to try to get the layout out you want. To run the code, go to the [Developer] tab and click the "Macros" icon and choose the macro to run. If you do not see the [Developer] tab, use: Office Button -- [Excel Options] and in the Popular group, choose to "Show Developer tab in the Ribbon". Sub IntoOneCell() Dim initialRange As Range Dim anyCell As Range Dim LC As Long Dim theDescription As String RemoveAddedRows ' cleanup just in case Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False Columns("E:E").ColumnWidth = 20 For Each anyCell In initialRange If Not IsEmpty(anyCell) Then 'start a group theDescription = _ Range("A1") & String(14, " ") & anyCell & vbLf theDescription = theDescription & _ Range("B1") & String(14, " ") & anyCell.Offset(0, 1) & vbLf theDescription = theDescription & _ Range("C1") & String(2, " ") & anyCell.Offset(0, 2) & vbLf theDescription = theDescription & _ Range("D1") & String(2, " ") & anyCell.Offset(0, 3) anyCell.Offset(0, 4) = theDescription End If Next Set initialRange = Nothing End Sub Sub IntoNewRows() Dim initialRange As Range Dim anyCell As Range Dim LC As Long RemoveAddedRows ' cleanup just in case Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False For LC = initialRange.Cells.Count To 2 Step -1 initialRange.Cells(LC, 1).EntireRow.Insert initialRange.Cells(LC, 1).EntireRow.Insert initialRange.Cells(LC, 1).EntireRow.Insert Next Set initialRange = Nothing 'get new range Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Columns("E:E").ColumnWidth = 20 For Each anyCell In initialRange If Not IsEmpty(anyCell) Then 'start a group anyCell.Offset(0, 4) = Range("A1") & String(9, " ") & anyCell anyCell.Offset(1, 4) = Range("B1") & String(9, " ") & anyCell.Offset(0, 1) anyCell.Offset(2, 4) = Range("C1") & String(9, " ") & anyCell.Offset(0, 2) anyCell.Offset(3, 4) = Range("D1") & String(2, " ") & anyCell.Offset(0, 3) End If Next Set initialRange = Nothing End Sub Sub RemoveAddedRows() Dim initialRange As Range Dim anyCell As Range Dim LC As Long Set initialRange = _ ActiveSheet.Range("A2:" & _ ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address) Application.ScreenUpdating = False For LC = initialRange.Cells.Count To 2 Step -1 If IsEmpty(initialRange.Cells(LC, 1)) Then initialRange.Cells(LC, 1).EntireRow.Delete End If Next Set initialRange = Nothing End Sub "urlocaljeweler" wrote: I am using Excel 2007. I have 5 columns as illustrated below - (Table Description has no rows yet) CTwt. Size Metal Jewelers cost Table Description 5 10 Tungsten 31 4 10.5 Tungsten 35 2 11 Tungsten 50 10 11.5 Tungsten 20 8 12 Tungsten 15 5 12.5 Tungsten 50 6 13 Tungsten 45 What I need to accomplish: Column 5(Table Description) needs to be a table in each row. For example Column 5, Row 1: (new table with 2 columns and 4 rows) like this: Ctwt. 5 Size 10 Metal Tungsten Jewelers Cost 31 I need to do this with hundreds of rows. After this I will somehow convert the table into html format and upload the excel file to my webpage.... Any ideas? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Distance Table | Excel Discussion (Misc queries) | |||
| Creating A New Table | Excel Worksheet Functions | |||
| Creating summary table from detail table | Excel Worksheet Functions | |||
| Creating a table from a formula | Excel Discussion (Misc queries) | |||
| Creating a Table | Excel Worksheet Functions | |||
Linear Mode
