Conditional Autosum

[GMOODY]

Good afternoon everyone
I've got a spreadsheet that has 3 columns in it that I need to put a conditional autosum. Basically, what I want to be able to do is say that if a cell is empty, put in the autosum function since there could be anywhere between 1 and 50 lines above to be summed together. There are also some lines in the data (that I can't remove) that don't have a quantity in them, so the only way I can think of is a conditional formatting.

Any help is greatly appreciated. I've attached a sample file. the dark coloured lines are added by a macro that I need to add onto. I want to say that any cell above the word "SKIDS" need to have the autosum function, or any cell directly below a number, or something along those lines.

[davegugg]

Try this:

Please Login or Register  to view this content.

Post this code into a standard module, then run it to populate the sums.

[GMOODY]

DaveGugg - Thank you for your help. I'm not very good with VBA (but I'm learning). I can see where you're going, but I'm having an error 1004 when it runs. When I hit "debug" the yellow highlighted line is

I haven't learned that much about the syntax in VBA yet, so I'm hoping you can help me out again.

Thanks again.

[davegugg]

I ran and have re-run the code in your example, and it doesn't give me any error.
Unfortunately, error 1004 is a very generic error and doesn't give much of a clue about what is happening. Maybe you can create an example exibiting this error?

[arthurbr]

FYI, the sum function ( like =sum(k:k) ) will sum all numeric values contained in that column, regardless of the presence of text in some cells.

As you didn't give an example of what you are trying to achieve, it's hard to give a precise answer.

[GMOODY]

Hi Dave
I've attached the file that I start with, and the big, ugly macro I'm using to create the pick lists for the warehouse. It works, but it's ugly. What I'm trying to do is make the cell above the word "SKIDS" have an autosum. Since I've entered it as the top 3 lines on the page though, I can't put the autosum formula (which whould just make everything so much easier). Ultimately, what I still have to figure out is how to do is put an autosum in column M and P in the line above the word "SKIDS", and get it to insert a page break at each change in Load # (Column B).

Instead of putting my macro in as normal, I've attached a notebook file so I don't take 5 pages. Also, I've got the value "i" further up in the macro, so I've changed the letter "i" in your earlier script to the letter "q".

Thanks again.

[GMOODY]

Sorry about that, I guess it would help if I included the file that the macro is applied to.

[davegugg]

The problem was the header on your example is different than on your previous example. So instead of starting on row 2, you need to start on row 9:

Please Login or Register  to view this content.

The best way I can suggest cutting your code down is to get rid of all "Selection". Refer directly to the cell rather than selecting the cell then refering to your selection. For example this:

Please Login or Register  to view this content.

can be shortened to this:

Please Login or Register  to view this content.

Make sense?

[GMOODY]

Dave - Thanks a lot for all of your help. I've looked through the code, and it is all starting to make sense (and it works). Thanks again.