Dynamic Table Using Reference Cells to Adjust Size

[Foxmann250]

Hey guys,

I'm trying to make a dynamic table using visual basics and have it reference a specific cell. I'm not too savvy with VB so any help would be greatly appreciated. So far the macro I have recorded is this:

Code:

Sub LABEL_SAMPLES()

' Merge Proper Cells 1

Range("I7:J7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

'Merge Proper Cells 2

Selection.Merge
Range("K7:O7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

' Label First Cell Sample 1 and Use AutoFill to Populate the Right Number Samples

Selection.Merge
Range("I7:J7").Select
ActiveCell.FormulaR1C1 = "SAMPLE 1"
Range("I7:O7").Select
Range("K7").Activate
Selection.AutoFill Destination:=Range("I7:O19"), Type:=xlFillDefault
Range("I7:O19").Select
End Sub


But what I'd like to do is have the number "19" above actually reference cell F7. So if i put in cell F7 "20" for twenty different samples, the range for the auto fill will go from I7:O26. And if I type in cell F7 "25" for twenty five different samples, the range for the auto fill would change and fill I7:O31. Hopefully that makes sense.

Thanks in advance!

[p45cal]

try:
Sub LABEL_SAMPLES()
For Each are In Range("I7:J7,K7:O7").Areas
With are
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next are
Range("I7").Value = "SAMPLE 1"
Range("I7:O7").AutoFill Destination:=Range("I7:O18").Resize(Range("F7").Value), Type:=xlFillDefault
End Sub

[Foxmann250]

Thanks for the quick reply p45cal. I tried using that and am greeted with this issue:

14749

[p45cal]

Add a line after
Sub TEST()
being:
Dim are

(or remove Option Explicit from the top of the module)

[Foxmann250]

Ahhh easy fix, thanks so much :)

If I wanted the autofill to populate the cells sideways instead of downwards, do you know what part of the code needs to be adjusted?

[p45cal]

If I wanted the autofill to populate the cells sideways instead of downwards, do you know what part of the code needs to be adjusted?What? those merged cells copied across horizontally?!
Well, change that last autofill line to:
Range("I7:O7").AutoFill Destination:=Range("I7:O7").Resize(, Range("F7").Value * 7), Type:=xlFillDefault


ps. looking at that autofill line in my msg#2, it should be:
Destination:=Range("I7:O7").Resize(Range("F7").Value), Type:=xlFillDefault
intead of:
Destination:=Range("I7:O18").Resize(Range("F7").Value), Type:=xlFillDefault
but it's not critical and won't matter.

[Foxmann250]

Haha no no, those merged cells did exactly what I wanted. You did great. What I'm trying to do is reuse the same code with other cells and have them autofill sideways. I achieved the same thing by using your original code but then recording a macro that transposed them, I just thought there had to be an easier way.

To explain in a little more detail, Im making a custom dynamic table, where a user can input basically the # of fireplaces they wish to use (aka # of columns the table needs) and # of sets they'd like to burn in those fireplaces (aka # of rows the table needs) into some cells and hit a button and the table will form for them.

Right now I have it working with the table forming but am struggling to get the table to fill with data. If I only have 5 samples but 20 sets need to be run, then those 5 samples need to repopulate in that column 4 times. I'm struggling due to the customization aspect of the sheet. If you want to take a look, here's the file:

14754

Thanks for your help so far. I'm sure my code is a jumbled mess and there's easier ways to do things but it works so far at least.

[p45cal]

Attach a workbook showing how you'd want this to appear when you click the Generate button:
14758

[Foxmann250]

Here is my current file. It does everything I want except fill the table completely. I also had to design the shuffle button code to shuffle a bunch of cells in case someone has a bunch of samples and makes a huge table. It would be nice to have the code only shuffle the table the size that is made but that may be a lot of work.

Current: 14762

Here is what I would hope the table would auto populate to look like with the current cell inputs.

Want: 14761

Thanks :)

[p45cal]

See attached. Made changes to CLEAR_FORM, GENERATE_BURN_TABLE and SHUFFLE_BURN_ORDER.

[Foxmann250]

Wow, thanks! You took it and made it run much smoother. I've made a couple final additions to make the table look nice and to prevent users from breaking it via not inputting any data in the cells, so here's the final build if anyone wishes to use the tool later. It seems like it could be useful in other aspects as well. Thanks again for all your help p45cal. You're always a huge help. :)

14766