automatic Copy down in excel
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.
Re: automatic Copy down in excel
Excel VBA question moved to Office Development
Re: automatic Copy down in excel
Can you paste the code - pasting the code might give us a better picture.
Re: automatic Copy down in excel
Welcome to the forum!
You should use the .FillDown method of the range object to copy the formulas down.
Re: automatic Copy down in excel
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.
Re: automatic Copy down in excel
Quote:
Originally Posted by DKenny
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.
Re: automatic Copy down in excel
Hi Guys.
Here is the code. I thought it might help.
Regards.
Roland
Quote:
Originally Posted by whiteWay
Can you paste the code - pasting the code might give us a better picture.
VB Code:
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("")