Complex macro/function.

Oki doki. No clue where exactly to post this, so I'm gonna settle for the General section. This is mainly for all you advanced users. Well, atleast in my opinion since I have no clue how to do this. Any advice will be welcomed.

Alright. Mainly what I want is a template, Column A and Column C will get certain values, from row 5 onwards to..where ever it ends. Sometimes it's gonna be 5 row's worth of information, othertimes it might go upto a 100. The requirement is that: quantity (column A) is multiplied with length (column C) so that A5*C5, A6*C6 etc etc. to where I stop adding values. Thing is, at the bottom of column C(will explain later) I want a TOTAL SUM value of Column C's total length times it's respective quantity from column A. Soooo, to give you long but hopefully clear example. Total = (Ay1*Cy1)+(Ay2*Cy2)+(Ay3*Cy3)+... where y1 / y2 / y3 are the ongoing rows... I hope that makes sense. If not I will post a picture to further explain it.

Furthermore I would like the "Total:" string to be say, 5 lines beneath C5 (coz there are other text headers at the top) and each time you enter a value in the C column from C5 onwards the TOTAL: string will drop that amount of lines. So if TOTAL: were to be at C10, on a blank template, and I enter data in C5,C6 and C7, it drops down to C13.

Like I said. If you could only provide me with tips that would suffice. The more indepth of course would help, loads.

Thanks for your time!

Here's how I think I would build the template.

The requirement is that: quantity (column A) is multiplied with length (column C) so that A5*C5, A6*C6 etc etc. to where I stop adding values.

You don't say where you want the products put, so, assuming theya re put in column D, column D=A5*C5. Copy down the maximum number of rows you think you will ever need.

at the bottom of column C(will explain later) I want a TOTAL SUM value of Column C's total length times it's respective quantity from column A.

5 rows below the bottom most expected row, put, in column C, the formula =sum(D5:Dxxx)

This is your template file. Now, you enter the data in A and C, the products and sums are automatically calculated. When you are done entering the data, Select the unused rows between the data and the sum, and Edit->Delete->Entire row and save as a new file.

I assumed from your stated experience level that you are unfamiliar with using VBA to program some tasks. If you wanted to use this project to start learning VBA, then record a macro to do the delete rows step and see what you can learn about automating that step.

"You don't say where you want the products put, so, assuming theya re put in column D, column D=A5*C5. Copy down the maximum number of rows you think you will ever need." - The only problem I have with this is that that formula is only concerning those two cells. I want to create a formula, that is basicly dynamic in the sense that if I use 50 cells, it will use the same formula up to cell 50 and bring it to one seperate cell to show the SUM of calculated values throughout A and C.

:/ I'm asking too much, aren't I?

I believe this can be done.
If you right click on the tab of the sheet you are working on, and click on view code, then paste this code into the window that comes up it should work

Please Login or Register  to view this content.

Brilliant! The formula/code is working!!

One small hicup now, it seems that each time I enter a new value in qty (column a), the total SUM it calculates is duplicated beneath one another. I can delete all the SUM's except of course the one that has the 5 row split.

I'm not sure how correct my theory is but isn't there a way to "link" the "split 5 rows" command to a delete function, that everytime a qty is entered it moves down 5 rows and deletes the previous SUM? Just blowing smoke here, help a noob out plz

If you paste this line above End Sub, it will get rid of the sum if you 'create' a new line, to erase the previous sum. Sorry, I forgot about clearing it.

Please Login or Register  to view this content.

Many thanks mate! I couldnt have done it without your help. I knew the math part but that's as far as my knowledge of the problem stretched. Thanks again so much for your input.

I owe you.