automatic Copy down in excel

[roland.steenbeek]

I have written a macro that does some fancy formatting and adding columns etc. I want to copy down the formulas in two columns down to the end of the list. The last part of the macro will then copy the results of the formulas, paste special them as values and delete a couple of columns. What I am looking for is a piece of code that will take the two formulas and copy them down to the end of the list. In the moment there are two macros and I have to manually copy the formulas down by using the double click on the right hand corner of the cell function. I'd like to see it as one macro. I'd be happy to copy the code so you can see it if you have any idea what I want to do.

Any help would be greatly appreciated.
Regards.

[Hack]

Excel VBA question moved to Office Development

[whiteWay]

Can you paste the code - pasting the code might give us a better picture.

[DKenny]

Welcome to the forum!

You should use the .FillDown method of the range object to copy the formulas down.

[Ecniv]

Can't you copy the cell with the formula, then set a range to the area to paste it in and paste it?

Repeat for the second column...


If the columns are together, then use one copy and paste loop.

[roland.steenbeek]

Welcome to the forum!

You should use the .FillDown method of the range object to copy the formulas down.

Thanks DKenny.

I thought of that, but can't work out how to tell the code to always fill to the end of the data. I have to give it a range, but how to I say: Just keep filling until you get to the end.

I'm probably not going about it the right way.

[roland.steenbeek]

Hi Guys.

Here is the code. I thought it might help.

Regards.
Roland

Can you paste the code - pasting the code might give us a better picture.

Range(Selection, Selection.End(xlDown)).Select
    ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+19000000"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=VALUE(RIGHT(RC[-1],2)&""/""&+MID(RC[-1],5,2)&""/""&+LEFT(RC[-1],4))"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.NumberFormat = "d/m/yyyy"
    ActiveCell.Select
    ActiveCell.Offset(0, -1).Range("A1:B1").Select
    'Selection.AutoFill Destination:=ActiveCell.Range("")