Foxmann250
11-12-2015, 02:39 PM
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!
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!