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

[noreendeasy]

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

 

[noreendeasy]

Hey,

I changed the Column to 'B' as A referred to headings and

IT WORKED!!

Thank you so muach!!!!!

 

[noreendeasy]

Hi again,

I hope you're still there to help or if anyone else can! Pretty please

This worked brilliantly except for one time

It doesn't work if I have only one row of data it deletes the formulas and fills down either the headings or blank as I tried removing the headings. I have tidied up my code (I removed formating which I am using a different macro for):

Sub Formulas_Vlookup()
'
' Formulas_Vlookup Macro
'
'
Range("X11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],TCM_Blotter!C[-9],1,FALSE)"
Range("X11:X" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("Y11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-3]"
Range("Y11:Y" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("Z11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],TCM_Blotter!C[-11]:C[-6],5,FALSE)"
Range("Z11:Z" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("Z11").Select
Selection.Style = "Comma"
Range("Z11:Z" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AA11").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-15])"
Range("AA11:AA" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AB11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],TCM_Blotter!C[-13]:C[-8],6,FALSE)"
Range("AB11:AB" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AC11").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-16])"
Range("AC11:AC" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AD11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],TCM_Blotter!C[-15]:C[-3],13,FALSE)"
Range("AD11:AD" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AD11").Select
Selection.Style = "Comma"
Range("AD11:AD" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AE11").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-10])"
Range("AE11:AE" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AF11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],TCM_Blotter!C[-17]:C[-14],2,FALSE)"
Range("AF11:AF" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AF11").Select
Selection.NumberFormat = "m/d/yyyy"
Range("AF11:AF" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AG11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-31]"
Range("AG11:AG" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AH11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],TCM_Blotter!C[-19]:C[-15],4,FALSE)"
Range("AH11:AH" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AH11").Select
Selection.NumberFormat = "m/d/yyyy"
Range("AH11:AH" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AI11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-30]"
Range("AI11:AI" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Range("AI12").Select
ActiveWindow.SmallScroll Down:=-6
Range("X11").Select
End Sub

 

[steve the fish]

This around your fill will do it:

If Range("B12")<>"" then 
Range("X11:X" & (Range("B" & Rows.Count).End(xlUp).row)).FillDown
Else: End If

Sorry made mistake. That should be good!

 

[noreendeasy]

Fantastic! Thanks a million!

 

[steve the fish]

You don't need to fill each time by the way.

Range("X11:BB" & (Range("B" & Rows.Count).End(xlUp).Row)).FillDown

for example would work.

 

[noreendeasy]

Where would you put that? Also they are different formulas would it not just copy the X11 formula??

 

[steve the fish]

No no it would copy the formula in the cell above each time. The one in Row 11 in this case. It would go at the end. Compare this with yours.

Sub trythis()
    Range("X11").FormulaR1C1 = "=VLOOKUP(RC[-2],TCM_Blotter!C[-9],1,FALSE)"
    Range("Y11").FormulaR1C1 = "=RC[-1]=RC[-3]"
    Range("Z11").FormulaR1C1 = "=VLOOKUP(RC[-4],TCM_Blotter!C[-11]:C[-6],5,FALSE)"
    Range("Z11").Style = "Comma"
    Range("AA11").FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-15])"
    Range("AB11").FormulaR1C1 = "=VLOOKUP(RC[-6],TCM_Blotter!C[-13]:C[-8],6,FALSE)"
    Range("AC11").FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-16])"
    Range("AD11").FormulaR1C1 = "=VLOOKUP(RC[-8],TCM_Blotter!C[-15]:C[-3],13,FALSE)"
    Range("AD11").Style = "Comma"
    Range("AE11").FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-10])"
    Range("AF11").FormulaR1C1 = "=VLOOKUP(RC[-10],TCM_Blotter!C[-17]:C[-14],2,FALSE)"
    Range("AF11").NumberFormat = "m/d/yyyy"
    Range("AG11").FormulaR1C1 = "=RC[-1]=RC[-31]"
    Range("AH11").FormulaR1C1 = "=VLOOKUP(RC[-12],TCM_Blotter!C[-19]:C[-15],4,FALSE)"
    Range("AH11").NumberFormat = "m/d/yyyy"
    Range("AI11").FormulaR1C1 = "=RC[-1]=RC[-30]"
    
    If Range("B12") <> "" Then
        Range("X11:AI" & (Range("B" & Rows.Count).End(xlUp).Row)).FillDown
    Else: End If
    
    Range("X11").Select
 End Sub

 

[noreendeasy]

Awesome!!!! That's great thank you! Saves a lot of time pasting that in!! Thanks very much!!!!