How to create Table based on cell value

Dave Smith

New Member
Joined
Jul 5, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Hi Experts,

I need one help as I got stuck & I am not able to figure out how to proceed further.

I want to create table base on cell values where the number of rows and the column are based on cell value with certain fix header name.
As Shown in Image the user will enter number of cases in the cell then there will be 1,2,3,4.......
Also the user will enter number of machine based on the value enter the number of coulmns will be increased or decreased as machine1, machine2,.....

The table will have certain fix header as Case, Scenario, Liquid depth
Variable in the table will be number of rows in table( based on number of cases) & columns based on number of machines

Pl. have a look to the image.


Pl. let me know if any one has any doubts.

Regards,
Dave Smith.
 

Attachments

  • 568.JPG
    568.JPG
    15.5 KB · Views: 174

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I have this sheet with some the control parameters and fixed headings to use by the code.

20211230 VBA Create Table.xlsm
ABC
1No of Rows5
2No of Machines2
3
4Fixed Headings to use in table
5CaseScenarioLiquid Depth
6
7
8Put table at A10
9
10
11
Sheet1


And using this code to generate the table.

VBA Code:
Sub CreateTable()

    Dim hdg() As Variant
    Dim NoOfRows As Long
    Dim NoOfMachines As Long
    Dim hdgCols As Long
    Dim hdgRow As Long
    Dim sht As Worksheet
    Dim i As Long, j As Long
    Dim rng As Range
    
    
    Set sht = ActiveSheet
    NoOfRows = sht.Range("B1")
    NoOfMachines = sht.Range("B2")
    hdgRow = 10
    
    With sht
        hdg = .Range(.Cells(5, "A"), Cells(5, "A").End(xlToRight)).Value
        hdgCols = UBound(hdg, 2)
        .Range("A" & hdgRow).Resize(1, hdgCols).Value = Application.Index(hdg, 1, 0)
        
        For i = 1 To NoOfMachines
            .Cells(hdgRow, i + hdgCols).Value = "Machines " & i
        Next i
        
        For j = 1 To NoOfRows
            .Cells(j + hdgRow, 1).Value = j
        Next j
        
        Set rng = Range("A" & hdgRow).CurrentRegion
        
    End With
       
    sht.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "tblMachines"
    
End Sub
 
Upvote 0
Solution
I have this sheet with some the control parameters and fixed headings to use by the code.

20211230 VBA Create Table.xlsm
ABC
1No of Rows5
2No of Machines2
3
4Fixed Headings to use in table
5CaseScenarioLiquid Depth
6
7
8Put table at A10
9
10
11
Sheet1


And using this code to generate the table.

VBA Code:
Sub CreateTable()

    Dim hdg() As Variant
    Dim NoOfRows As Long
    Dim NoOfMachines As Long
    Dim hdgCols As Long
    Dim hdgRow As Long
    Dim sht As Worksheet
    Dim i As Long, j As Long
    Dim rng As Range
   
   
    Set sht = ActiveSheet
    NoOfRows = sht.Range("B1")
    NoOfMachines = sht.Range("B2")
    hdgRow = 10
   
    With sht
        hdg = .Range(.Cells(5, "A"), Cells(5, "A").End(xlToRight)).Value
        hdgCols = UBound(hdg, 2)
        .Range("A" & hdgRow).Resize(1, hdgCols).Value = Application.Index(hdg, 1, 0)
       
        For i = 1 To NoOfMachines
            .Cells(hdgRow, i + hdgCols).Value = "Machines " & i
        Next i
       
        For j = 1 To NoOfRows
            .Cells(j + hdgRow, 1).Value = j
        Next j
       
        Set rng = Range("A" & hdgRow).CurrentRegion
       
    End With
      
    sht.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "tblMachines"
   
End Sub
Alex, Thanks a lot it completely worked for me :)
 
Upvote 0
Hey Alex, thank you for the code but can i ask little bit for more help ?
actually i need to keep this table at "I" column but number of machines column is not moving towards right it remains at D column only & rest of the column shifts.
can you pl. help i am not able to figure out.

pl. have a look in image
 

Attachments

  • 254.JPG
    254.JPG
    70.3 KB · Views: 53
Upvote 0
I had considered using a variable for the output column position. Ah well.
I have modified the code so the output position is now dependant on
hdgRow & firstCol ( where Column 9 = Column "I").

I have also made some other changes which includes giving you the option of changing from having your fixed predefined column headings in a range to hard coding those headings in the code.
I have left it with Option 1 which is the range, just comment / uncomment the 3 lines for the option you want to use.

VBA Code:
Sub CreateTable()

    Dim hdgArrTemplate() As Variant
    Dim NoOfRows As Long
    Dim NoOfMachines As Long
    Dim hdgCols As Long
    Dim hdgRow As Long
    Dim sht As Worksheet
    Dim i As Long, j As Long
    Dim rng As Range
    Dim firstCol As Long
    
    Set sht = ActiveSheet
    NoOfRows = sht.Range("B1")
    NoOfMachines = sht.Range("B2")
    hdgRow = 10                         ' <--- Set outout Row for table
    firstCol = 9                        ' <--- Set output Column for table
    
    With sht
    
        ' Option 1 - Using a range containing the fixed headings
        hdgArrTemplate = .Range(.Cells(5, "A"), Cells(5, "A").End(xlToRight)).Value
        hdgCols = UBound(hdgArrTemplate, 2)
        .Cells(hdgRow, firstCol).Resize(1, hdgCols).Value = Application.Index(hdgArrTemplate, 1, 0)
        
'        ' Option 2 - Hard coding the fixed headings in the code
'        hdgArrTemplate = Array("Case", "Scenario", "Liquid Depth")
'        hdgCols = UBound(hdgArrTemplate) + 1
'        .Cells(hdgRow, firstCol).Resize(1, hdgCols).Value = hdgArrTemplate
        
        For i = 1 To NoOfMachines
            .Cells(hdgRow, i + firstCol + hdgCols - 1).Value = "Machines " & i
        Next i
        
        For j = 1 To NoOfRows
            .Cells(j + hdgRow, firstCol).Value = j
        Next j
        
        Set rng = Cells(hdgRow, firstCol).CurrentRegion
        
    End With
       
    sht.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "tblMachines"
    
End Sub
 
Upvote 0
Thank
I had considered using a variable for the output column position. Ah well.
I have modified the code so the output position is now dependant on
hdgRow & firstCol ( where Column 9 = Column "I").

I have also made some other changes which includes giving you the option of changing from having your fixed predefined column headings in a range to hard coding those headings in the code.
I have left it with Option 1 which is the range, just comment / uncomment the 3 lines for the option you want to use.

VBA Code:
Sub CreateTable()

    Dim hdgArrTemplate() As Variant
    Dim NoOfRows As Long
    Dim NoOfMachines As Long
    Dim hdgCols As Long
    Dim hdgRow As Long
    Dim sht As Worksheet
    Dim i As Long, j As Long
    Dim rng As Range
    Dim firstCol As Long
   
    Set sht = ActiveSheet
    NoOfRows = sht.Range("B1")
    NoOfMachines = sht.Range("B2")
    hdgRow = 10                         ' <--- Set outout Row for table
    firstCol = 9                        ' <--- Set output Column for table
   
    With sht
   
        ' Option 1 - Using a range containing the fixed headings
        hdgArrTemplate = .Range(.Cells(5, "A"), Cells(5, "A").End(xlToRight)).Value
        hdgCols = UBound(hdgArrTemplate, 2)
        .Cells(hdgRow, firstCol).Resize(1, hdgCols).Value = Application.Index(hdgArrTemplate, 1, 0)
       
'        ' Option 2 - Hard coding the fixed headings in the code
'        hdgArrTemplate = Array("Case", "Scenario", "Liquid Depth")
'        hdgCols = UBound(hdgArrTemplate) + 1
'        .Cells(hdgRow, firstCol).Resize(1, hdgCols).Value = hdgArrTemplate
       
        For i = 1 To NoOfMachines
            .Cells(hdgRow, i + firstCol + hdgCols - 1).Value = "Machines " & i
        Next i
       
        For j = 1 To NoOfRows
            .Cells(j + hdgRow, firstCol).Value = j
        Next j
       
        Set rng = Cells(hdgRow, firstCol).CurrentRegion
       
    End With
      
    sht.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "tblMachines"
   
End Sub
Thanks a lot Alex, for helping me in figuring it out thanks a lot. :)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top