How to sum multiple columns automatically

[BBoydAnchor]

Where I work we perform a monthly inventory count and I must create a report for the production managers. In this report I have multiple columns to sum and after 5 years of doing this manually, it has gotten old. There has to be a way to search out these cells with code and perform this task automatically, but I'm not sure how to do it. While watching a video on VBA, I found the following code promising and can find the first empty cell when running it, but not sure what to do to insert the function and if I did, I'm not sure how to search out for the next cell to enter the function.

Please Login or Register  to view this content.

By recording a macro, I can see the code to add the sum, but still lost as what needs to be done.

Please Login or Register  to view this content.

I have attached a spreadsheet with sample data with highlighted cells that need a sum of the cells above it.

I cannot upload a spreadsheet or image from my work computer so hopefully the explanation will be good enough. If not, I will upload it from home later tonight.

BTW... if the cells that are required to be SUMMED were always the same, I could just use a recorded macro to do the job. However, because inventory items and added and deleted throughout any given month, these locations are not always the same.

Brian

[Leith Ross]

Hello BBoydAnchor ,

Welcome to the Forum!

Here is a macro that will ask you for the first cell of the column you want the sum for. This will display the sum from the starting cell to the last cell with data in the column. Your needs may be different and the code may need to adjusted accordingly but at least it is a start.

Please Login or Register  to view this content.

[BBoydAnchor]

I appreciate the effort, but it's not exactly what I need. I was hoping to upload the spreadsheet from home over the weekend, but was not able to. The code you provided puts the sum in a message box and I need the cell to be filled with the sum.

Below is code that sort of does what I need it to. Cell "F4" will always be the same so the code can start there. It is the other cells like "F15", "F16" and so on that will change each month. I know that "Offset" is needed to be used to move down to the next empty cell to add the SUM formula, but not sure how it should be coded and I'm not sure if the SUM formula is correct.

Please Login or Register  to view this content.

[BBoydAnchor]

OK. I did a little more reading and figured it out. However, there is a lot more code than there probably should be and the SUM function is hard coded for only 10 cells. Also I would like to include a Grand Total that includes all the sums for each department.

These columns show the inventory values before and after the count.

Please Login or Register  to view this content.

[BBoydAnchor]

This problem solved at...
http://www.excelforum.com/excel-prog...13#post2669213