Thread: Create a table with size based on a formula (VBA)

Hi,

I need a macro to create a table with a size based on a formula and in which the values in the table are also formulas. An example is provided in attachment.

How can I do this?

Thanks

You will have to be more explicit in the details of what you are wanting to accomplish. Your attachment does not help to explain or clarify your post.

Please provide a more clear explanation and upload a revised attachment that includes an example of the results you want.

How critical is the "formula" part of this? Once you move to VBA, it's simpler to just put the values directly into the table.

I was simply referring to the fact that once you move to VBA you can edit your "data" and rerun your macro to reassert your table at anytime, there's no need to maintain formulas in the table itself, it seems like plumbing you just don't need once you go to VBA.

This shows what I mean. I moved all the Input cells to the left so they do not interfere with a table size that could be any size. This will create the table starting in column H and go out as far as needed.

1) Put data in column B
2) Fill out the start, end and increments section
3) Click the button to create the table.
4) Change any of the inputs and click the button again to reassert the table

Option Explicit

Sub CreateTable()
'Jerry Beaucaire 5/8/2010
'Take raw data and reformat into a 2D table
Dim tbItm  As Long
Dim dataRNG As Range
Dim vStart  As Double:  vStart = [D2]
Dim vEnd    As Double:  vEnd = [E2]
Dim vIncr   As Double:  vIncr = [F2]
Dim hStart  As Double:  hStart = [D3]
Dim hEnd    As Double:  hEnd = [E3]
Dim hIncr   As Double:  hIncr = [F3]
Application.ScreenUpdating = False

If [I3] <> "" Then [I3].CurrentRegion.EntireColumn.Delete xlShiftToLeft
Set dataRNG = Range("B2", Range("B" & Rows.Count).End(xlUp))

For tbItm = 1 To vEnd / vIncr + 1
    Range("H2").Offset(tbItm, 0) = vStart + (vIncr * (tbItm - 1))
Next tbItm
    
For tbItm = 1 To hEnd / hIncr + 1
    Range("H2").Offset(0, tbItm) = hStart + (hIncr * (tbItm - 1))
Next tbItm

vIncr = Application.WorksheetFunction.CountA(Range("H:H"))

For tbItm = 1 To dataRNG.Cells.Count / vIncr
    Range("B2").Offset((tbItm - 1) * vIncr).Resize(vIncr).Copy
    Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteAll
Next tbItm

Set dataRNG = Nothing
Range("I3").CurrentRegion.Columns.AutoFit
Range("H:H").HorizontalAlignment = xlCenter
Range("H2", Cells(2, Columns.Count)).HorizontalAlignment = xlCenter
Application.ScreenUpdating = True
End Sub