macro to fill in empty cells with certain formulas or values in.

Hi all
I wonder if someone can help me out. What would be my best option/method to achieve the following below?

I would like to have a macro that would fill in some empty cells with certain formulas or values. Macro should look the first available empty cell in columns F,G,H,I,J,K,L,M and populate them as far as column E goes. Below is the breakdown of formulas or values that should go to cells of each column.

Please Login or Register  to view this content.

Let me present illustrative example:


Before macro

--D ------ E ----------- F ---------- G -------- H ------- I -------- J -------- K -------- L -------- M
300 ---- 39 ---- Planning ---- OGL ---- 5 ------ 46 ---- Wed ---- 2011 ---- M60 ---- S60
420 ---- 39 ---- Planning ---- OGL ---- 7 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
90 ------ 5C --- Machines --- OGL ---- 1.5 ---- 46 ---- Thu ----- 2011 ---- M60 ---- S60
720 ---- 39 ---- Planning ---- OGL ---- 12 ----- 46 ---- Thu ----- 2011 ---- M60 ---- S60
90 ------ 5C --- Machines --- OGL ---- 1.5 ---- 46 ---- Thu ----- 2011 ---- M60 ---- S60
120 ---- 12 ---- Machines --- OGL ---- 2 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
300 ---- 23 ---- Manning ----- OGL ---- 5 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
420 ---- 39 ----
300 ---- 5C ----
420 ---- 39 ----


After macro

--D ------ E ----------- F ---------- G -------- H ------- I -------- J -------- K -------- L -------- M
300 ---- 39 ---- Planning ---- OGL ---- 5 ------ 46 ---- Wed ---- 2011 ---- M60 ---- S60
420 ---- 39 ---- Planning ---- OGL ---- 7 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
90 ------ 5C --- Machines --- OGL ---- 1.5 ---- 46 ---- Thu ----- 2011 ---- M60 ---- S60
720 ---- 39 ---- Planning ---- OGL ---- 12 ----- 46 ---- Thu ----- 2011 ---- M60 ---- S60
90 ------ 5C --- Machines --- OGL ---- 1.5 ---- 46 ---- Thu ----- 2011 ---- M60 ---- S60
120 ---- 12 ---- Machines --- OGL ---- 2 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
300 ---- 23 ---- Manning ----- OGL ---- 5 ------ 46 ---- Thu ----- 2011 ---- M60 ---- S60
420 ---- 39 ---- Planning ---- OGL ----- 7 ------ 46 ---- Fri ------ 2011 ---- M60 ---- S60
300 ---- 5C ---- Machines --- OGL ---- 5 ------ 46 ---- Fri ------ 2011 ---- M60 ---- S60
420 ---- 39 ---- Planning ---- OGL ----- 7 ------ 46 ---- Sat ----- 2011 ---- M60 ---- S60


It would be nice if the macro would populate the cells with the required formulas and would then only keep the values (a la copy-paste-special-values). I have attached an example spreadsheet. Idea is that user will fill in some some cells (cells from col A to col E) on this ever growing spreadsheet and macro will populate the rest (col F to col M).



Any help would be much appreciated.
Cheers

Like so:

Please Login or Register  to view this content.

I came up with this.

Please Login or Register  to view this content.

Edit: Go with JBeaucaire's Macro it is much better code than mine.
On top of that mine is over writing your hard coded stuff