freaknyea
03-22-2011, 09:55 AM
I need to copy a formula down without specifying the columns that they will be copied down in. I'm trying to make this macro based on the cell that I have active at the time of initiating the macro.
You can see below where I'm stuck. Originally, I made the macro just from clicking through cells, but now I'm trying to unspecify everything.
Anyone able to help me out?
Sub DAYMONTHYEAR()
'
' DAYMONTHYEAR Macro
'
'
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 1).FormulaR1C1 = "Month"
ActiveCell.Offset(0, 2).FormulaR1C1 = "Day"
ActiveCell.Offset(0, 3).FormulaR1C1 = "Year"
ActiveCell.Offset(1, 1).FormulaR1C1 = "=MONTH(RC[-1])"
ActiveCell.Offset(1, 2).FormulaR1C1 = "=DAY(RC[-2])"
ActiveCell.Offset(1, 3).FormulaR1C1 = "=YEAR(RC[-3])"
ActiveCell.Offset(1, 1).Resize(1, 3).Select
' ***Stuck Here***
Selection.AutoFill Destination:=Range("B2:D45"), Type:=xlFillDefault
Range("B2:D45").Select
ActiveWindow.SmallScroll Down:=-45
Selection.NumberFormat = "General"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "New Date"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E45"), Type:=xlFillDefault
Range("E2:E45").Select
ActiveWindow.SmallScroll Down:=-39
Range("F2").Select
End Sub
You can see below where I'm stuck. Originally, I made the macro just from clicking through cells, but now I'm trying to unspecify everything.
Anyone able to help me out?
Sub DAYMONTHYEAR()
'
' DAYMONTHYEAR Macro
'
'
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 1).FormulaR1C1 = "Month"
ActiveCell.Offset(0, 2).FormulaR1C1 = "Day"
ActiveCell.Offset(0, 3).FormulaR1C1 = "Year"
ActiveCell.Offset(1, 1).FormulaR1C1 = "=MONTH(RC[-1])"
ActiveCell.Offset(1, 2).FormulaR1C1 = "=DAY(RC[-2])"
ActiveCell.Offset(1, 3).FormulaR1C1 = "=YEAR(RC[-3])"
ActiveCell.Offset(1, 1).Resize(1, 3).Select
' ***Stuck Here***
Selection.AutoFill Destination:=Range("B2:D45"), Type:=xlFillDefault
Range("B2:D45").Select
ActiveWindow.SmallScroll Down:=-45
Selection.NumberFormat = "General"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "New Date"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E45"), Type:=xlFillDefault
Range("E2:E45").Select
ActiveWindow.SmallScroll Down:=-39
Range("F2").Select
End Sub