Macro to paste a comparison formula into a selected range

Good afternoon all,

I have a repetitive task that I am trying to see about automating.

In many spreadsheets with many imports of different types of data, I have to perform a simple differencing calculation abs((Current Year)-(Prior Year)) I would like to be able to use a macro which will take a formula of this form:

Please Login or Register  to view this content.

And paste it into a selected column of cells.

For example, in the import of data, I may have to do this calculation for 3 columns of data. I want to be able to select a range and run a macro which will insert the formula into the range of cells and calculate an absoulte value difference for each row/record. I've thusly scrapped all of the macros I've tried recording/tweaking to make this work. Also, not sure if this is important, but the offset is not always E2. That is just the example. I want the offset function to be self-referencing to the cell it is located in. Please let me know if I've confused you, I appreciate your assistance!

Can do Arlette, thank you! I've built and attached a really quick example. I might recieve output from our accounting software which contains year-over-year comparison data for a company. In the sample spreadsheet, column D would be blank initially. What I would like the macro to be able to do is allow me to select a range by mouse/highlight (in the sample spreadsheet D2:D6), and then paste the function into it. I guess what I would need is for the code to be flexible enough to allow for different lengths and values of column selects and for the formula pasted to reference the cell it is pasted into. I didn't know if it was possible, but since my output consistently will follow the row order of Current Year then Prior Year, I wanted to have the formula itself hard-coded into the macro itself to save the time of writing it in each column and copying down so frequently. Hope this makes it a little bit more clear.