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

N

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try:

Code: 
Range("B1:B" & (Range("A" & Rows.Count).End(xlUp).Row)).FillDown

You will need to change this to suit so run on a copy of your workbook first!
 
Upvote 0
You have quite a few places you will need to alter.

Anywhere you see:

Code: 
Selection.AutoFill
 
Upvote 0
Perfect, thanks. how do I edit it? sorry it doesn't work in it's current form. I don't understand what it's saying. Could you please detail how I would edit it? Thank you very much for your help?
 
Upvote 0
You will need to change this bit:

"B1:B"

to whatever column you want to fill with the first cell you want to filldown. In your case X11 for example so:

"X11:X"

You then need to change:

"A"

to a column in your data that is complete. "A" may be ok.
 
Upvote 0
Sub CHECK()
'
' CHECK Macro
'
'
Range("X10").Select
Sheets("TCM_Blotter").Select
Range("O1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transaction Analysis").Select
Sheets("TCM_Blotter").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transaction Analysis").Select
Range("X10").Select
ActiveSheet.Paste
End Sub
Sub Vlookup_Formulas()
'
' 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:X" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
 Range("X11:X16").Select
Range("Y11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-3]"
Range("Y11").Select
Selection.AutoFill Destination:=Range("Y11:Y" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:Z" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AA" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AB" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
Range("AB11:AB16").Select
Range("AC11").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-16])"
Range("AC11").Select
Selection.AutoFill Destination:=Range("AC11:AC" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AD" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AE" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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("B1:B" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AG" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AH" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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:AI" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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

This is my updated code, it's coming up with an error now:
Run-time error '1004': </SPAN>
AutoFill method of Range class failed

Have I done something incorrect?</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 
Upvote 0
This:
Code: 
Selection.AutoFill Destination:=Range("AE11:AE" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown

just needs to be:
Code: 
Range("AE11:AE" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown

so long as column "A" has data.
 
Upvote 0
Hi, that's awesome, I did that and it worked sort of. Let me explain. It worked for the filling. The only thing is my column A has a heading and it autofilled this! So my data starts on A11 but when I edited it to this:
Range("X11:X" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown

I got this error:
Run-time Error '1004'
Method 'Range' of object '_Global' failed

My code now looks like:
Sub Vlookup_Formulas()
'
' 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
Range("X11:X" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
Range("X11:X16").Select
Range("Y11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-3]"
Range("Y11").Select
Range("Y11:Y" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
Range("Y11:Y16").Select
Range("Z11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],TCM_Blotter!C[-11]:C[3],5,FALSE)"
Range("Z11").Select
Range("Z11:Z" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AA11:AA" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AB11:AB" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
Range("AB11:AB16").Select
Range("AC11").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])=ABS(RC[-16])"
Range("AC11").Select
Range("AC11:AC" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AD11:AD" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("X11:X" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AF11:AF" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
Range("B1:B" & (Range("A" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AG11:AG" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
Range("AH11:AH" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
Range("AH11:AH16").Select
Selection.NumberFormat = "m/d/yyyy"
Range("AI11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-30]"
Range("AI11").Select
Range("AI11:AI" & (Range("A11" & Rows.Count).End(xlUp).row)).FillDown
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
 
Upvote 0
This bit:

Range("A" & Rows.Count).End(xlUp).Row

is just to produce the row number of the last used row in column "A".

Do not change this bit to a cell reference. Leave it as as above.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA autofilling code
Replies
6
Views
275
Peter_SSs
Peter_SSs
J
  • Question
VBA removing heading in spreadsheet
Replies
3
Views
215
rlv01
R
T
  • Question
ActiveSheet. Paste error
Replies
1
Views
317
RoryA
RoryA
S
  • Question
Macro Formatting to all WS
Replies
1
Views
196
Micron
M
P
  • Question
Help with Vba Code
Replies
3
Views
399
Coyotex3
C

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top