Pasting a formula row every third line

[tallgingerbloke]

Hi,

I have a large spreadsheet that is very slow to work on due to the amount of calculations in it. I have overcome this part by removing the formula and leaving just the data. There is however one line of formula that I would like to keep in the spreadsheet and I think it would be easier to re-introduce it after.

So, any ideas on how i can insert a strip of formula while leaving the rest of the spreadsheet. I cannot do it manually as I have bout 10,000 lines.

Attached is a simplified version to show what I mean. I realize it may be a macro, which would be fine

hope I have made the situation clear, and thanks for reading.

Cheers

Tim

[stanleydgromjr]

tallgingerbloke,

The macro searches in column B for "running total" and then adds the formulae to the columns to the right.

Detach/open workbook AddFormulae - VP column B - running total right columns - tallgingerbloke - EF783977 - SDG15.xls and run the AddFormulae macro.

Then make chages to the "deliveries".


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AddFormulae macro.


Then make chages to the "deliveries".

[martindwilson]

you could just do sorting and auto fill formulas
call column B "sort" in B2
numbering in a spare column eg g3 from 1 down to 10000 or so label that column as "unsort"in g2
then select all data starting a2 to end including unsort column
now sort by sort column
all the running total cells will be together
put formula appropriate for the row it is now in in columns c d e f
drag those down to complete all the running totals
now select the lot and sort by unsort

[tallgingerbloke]

Fantastic Stanley, thanks ever so much, works really well

[stanleydgromjr]

tallgingerbloke,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.