ExcelBanter - Calc cost pro rata (nested IF's and macro to find empty row)

Hi all,

I was helped out earlier on the General Discussion forum so I thought I'd upload my greatest excel achievment!

At work we had a simple calculator to work out the cost of a product over a date range and calculate it acurately on a per day basis. I wanted to improve upon this so that only a from and to date had to be entered and I have done, plus it calculates many products at once.

There are also macros now to paste lines into another workbook which can work out the next empty row. Using a counter wasn't an option as some lines would be added manually.

There are obvious limitations with this due to IF. Also if the price of a product chages during the period you are calculating so have to use the Original sheet. If anyone can make my meager contribution more ellagant then please feel free to assimilate and regurgetate! I've really go to work on the presentation of this as well.

Most of the formulas are hidden below and to the right of the visable sheets, but there are other hidden sheets you may want to look at. The main formulas are;

To work out start month for use in CONCATENATE:
=IF(C10<"",A10,IF(C9<"",A9,IF(C8<"",A8,IF(C7<" ",A7,IF(C6<"",A6,IF(C5<"",A5,IF(C4<"",A4))) ))))

To work out first broken period rental:
=ROUND(IF(A4=Original!I4,C4/Original!L4*(Original!J4+1-A4),IF(A4=Original!I5,C4/Original!L5*(Original!J5+1-A4),IF(A4=Original!I6,C4/Original!L6*(Original!J6+1-A4),IF(A4=Original!I7,C4/Original!L7*(Original!J7+1-A4),IF(A4=Original!I8,C4/Original!L8*(Original!J8+1-A4),IF(A4=Original!I9,C4/Original!L9*(Original!J9+1-A4),IF(A4=Original!I10,C4/Original!L10*(Original!J10+1-A4),""))))))),2)

To work our whole month rentals:
=IF(A4=Original!I6,Original!M5,IF(A4=Original!I7 ,Original!M6,IF(A4=Original!I8,Original!M7,IF(A4 =Original!I9,Original!M8,IF(A4=Original!I10,Origi nal!M9,IF(A4=Original!I11,Original!M10,IF(A4=Ori ginal!I12,Original!M11)))))))
=IF(B4Original!J6,Original!M5,IF(B4Original!J7,O riginal!M6,IF(B4Original!J8,Original!M7,IF(B4Ori ginal!J9,Original!M8,IF(B4Original!J10,Original!M 9,IF(B4Original!J11,Original!M10,IF(B4Original!J 12,Original!M11,8)))))))
=C4*(E42-E43)
C4 is the cost and E42+43 are the values returned from the above

After all that I can't upload the rental calculator, only room for the bulk calculator. Oh well the formula I used to get the next free row was:

Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select