Excel: Fill down a formula in a macro for an undetermined set of rows


Hi there, I was wondering how to fill down a formula in a recorded macro which I tried using the auto fill but this only fills down for the previous amount of data i.e. if I had 6 rows of data it only fills the formulas down for this number even if it changes. My code looks like this: Vlookup_Formulas Macro ' ' Range("T24").Select Sheets("TCM_Blotter").Select Range("O1").Select Application.CutCopyMode = False Selection.Copy Sheets("Transaction Analysis").Select Range("X10").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Range("Y10").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "TRANSACTION_NUMBER_CHECK" Range("Z10").Select Sheets("TCM_Blotter").Select Range("S1").Select Selection.Copy Sheets("Transaction Analysis").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "TRADE_QUANTITY_CHECK" Range("Z16").Select Sheets("TCM_Blotter").Select Range("T1").Select Selection.Copy Sheets("Transaction Analysis").Select Range("Z10").Select Application.CutCopyMode = False Selection.Copy Range("AA10").Select ActiveSheet.Paste Range("Z10").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "TRADE_QUANTITY" Range("Z11").Select Columns("Z:Z").EntireColumn.AutoFit Range("P34").Select Sheets("TCM_Blotter").Select Selection.Copy Sheets("Transaction Analysis").Select Range("AB10").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Range("AC10").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "PRICE_CHECK" Range("AD10").Select Sheets("TCM_Blotter").Select Range("AA1").Select Selection.Copy Sheets("Transaction Analysis").Select Range("AD10").Select ActiveSheet.Paste Range("AD20").Select Sheets("Transaction Analysis").Select Range("AD10").Select Application.CutCopyMode = False Selection.Copy Range("AE10").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "NET_SETT_AMOUNT_CHECK" Range("AF10").Select Sheets("TCM_Blotter").Select Range("P1").Select Selection.Copy Sheets("Transaction Analysis").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Range("AG10").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "TRADE_DATE_CHECK" Range("AH10").Select Sheets("TCM_Blotter").Select Range("R1").Select Selection.Copy Sheets("Transaction Analysis").Select ActiveSheet.Paste Range("AI10").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "SETTLEMENT_DATE_CHECK" Range("X10").Select Range(Selection, Selection.End(xlToRight)).Select With Selection .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("X:X").EntireColumn.AutoFit Columns("X:X").ColumnWidth = 16 Range("Y10").Select Columns("Y:Y").EntireColumn.AutoFit Columns("Y:Y").ColumnWidth = 21 Columns("Y:Y").ColumnWidth = 23 ActiveWindow.SmallScroll Down:=-21 Range("X11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],TCM_Blotter!C[-9]:C[5],1,FALSE)" Range("X11").Select Selection.AutoFill Destination:=Range("X11:X16") Range("X11:X16").Select Range("Y11").Select ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-3]" Range("Y11").Select Selection.AutoFill Destination:=Range("Y11:Y16") Range("Y11:Y16").Select Range("Z11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],TCM_Blotter!C[-11]:C[3],5,FALSE)" Range("Z11").Select Selection.AutoFill Destination:=Range("Z11:Z16") Range("Z11:Z16").Select Selection.Style = "Comma" Columns("AA:AA").ColumnWidth = 17 Columns("AA:AA").ColumnWidth = 18.14 Range("AA11").Select ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-15])" Range("AA11").Select Selection.AutoFill Destination:=Range("AA11:AA16") Range("AA11:AA16").Select Range("AB11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(TCM_Blotter!C[-13]:C[1],6,FALSE)" Range("AB11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],TCM_Blotter!C[-13]:C[1],6,FALSE)" Range("AB11").Select Selection.AutoFill Destination:=Range("AB11:AB16") Range("AB11:AB16").Select Range("AC11").Select ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-16])" Range("AC11").Select Selection.AutoFill Destination:=Range("AC11:AC16") Range("AC11:AC16").Select Columns("AB:AB").EntireColumn.AutoFit Columns("AC:AC").EntireColumn.AutoFit Range("AD11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],TCM_Blotter!C[-15]:C[-1],13,FALSE)" Range("AD11").Select Selection.AutoFill Destination:=Range("AD11:AD16") Range("AD11:AD16").Select Selection.Style = "Comma" Range("AE11").Select Columns("AD:AD").ColumnWidth = 27.14 Columns("AD:AD").ColumnWidth = 19.29 Columns("AD:AD").ColumnWidth = 15.71 Range("AD10").Select Columns("AD:AD").ColumnWidth = 13.86 Columns("AD:AD").ColumnWidth = 12.57 Range("AE1").Select Columns("AD:AD").ColumnWidth = 11.29 Range("AE11").Select Columns("AE:AE").ColumnWidth = 12.14 Columns("AE:AE").ColumnWidth = 15.14 ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-13])" Range("AE11").Select Selection.AutoFill Destination:=Range("AE11:AE16") Range("AE11:AE16").Select Range("AF11").Select ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],TCM_Blotter!C[-17]:C[-3],2,FALSE)" Range("AF11").Select Selection.AutoFill Destination:=Range("AF11:AF16") Range("AF11:AF16").Select Selection.NumberFormat = "m/d/yyyy" Range("AG11").Select Columns("AG:AG").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-31]" Range("AG11").Select Selection.AutoFill Destination:=Range("AG11:AG16") Range("AG11:AG16").Select Range("AH11").Select Columns("AH:AH").ColumnWidth = 10.71 Columns("AG:AG").ColumnWidth = 11.43 Columns("AG:AG").ColumnWidth = 12.86 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],TCM_Blotter!C[-19]:C[-5],4,FALSE)" Range("AH11").Select Selection.AutoFill Destination:=Range("AH11:AH16") Range("AH11:AH16").Select Selection.NumberFormat = "m/d/yyyy" Range("AI11").Select ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-30]" Range("AI11").Select Selection.AutoFill Destination:=Range("AI11:AI16") Range("AI11:AI16").Select Range("X11").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With Range("X10").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("X10:Z10").Select Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("AB24").Select End Sub Thanks in advance of your help. Noreen


This question generated 18 answers. To proceed to the answers, click here.

This thread is current as of September 01, 2014.


For more resources for Microsoft Excel