Calculate sum and insert text in multiple sheets

**timtim89** · 01-26-2012, 08:38 AM

Hi!

In all the sheets in the workbook I need to check column F for containing a cell of value "Commission". If it does, the numbers in the cells of the column following "Commission" needs to be summed together and the sum is insertet in the first blank cell following the numbers that are summed.
Also, if the sum is calculated, in column A I need insertet a text two rows following last cell containing value. It is better shown in the attached workbook. The code is as follows (but not working):

Please Login or Register  to view this content.

Any help is appreciated! Thanks in advance.

**arlu1201** · 01-27-2012, 09:12 AM

Put this code in a blank module of your file (Press Alt + F11, a code window will open. On the left hand side, you will see Microsoft Excel Objects, right click and select Insert -> Module. Copy this code there)

Please Login or Register  to view this content.

**timtim89** · 01-27-2012, 11:25 AM

Hey Arlette. Thank you so much for the code.
In the workbook in which I'll use the code I have additional sheets, in which there is no "Commission" found. I got an error message, and added

Please Login or Register  to view this content.

but this does that the text (and sometimes sum) are inserted anyway in the sheets. How do I avoid this? That the text and sum are not inserted in sheets in which "Commission" is not found? My bad for the attachment no corresponding to the real data, and thanks again!

**arlu1201** · 01-28-2012, 02:06 PM

Change On Error Resume Next to On Error goto errhandler and then before next i put this line Errhandler:

**timtim89** · 01-28-2012, 02:17 PM

Thank you. It works perfect now

**timtim89** · 01-30-2012, 06:30 AM

Now that I'm applying the macro on the data that I actually need it for, I get an run time error 91 saying "Object variable or With block variable not set". This I got after adding a sheet to the workbook in which the data worked. The macro still works as supposed to, but anyone who can tell me how to get rid of the error message? I attached the workbook once again with the new data and code. Any help is appreciated, and once again: thanks a lot in advance

Please Login or Register  to view this content.

**timtim89** · 02-05-2012, 12:34 PM

If it helps in any way, I'm also getting the error message if I simply add an empty sheet to the first three sheets (for which the code works).

**OnErrorGoto0** · 02-08-2012, 10:10 AM

That is not how you use an error handler, but you don't need one here anyway

Please Login or Register  to view this content.

**dangelor** · 02-08-2012, 10:13 AM

Try handling the errors this way...

Please Login or Register  to view this content.

**timtim89** · 02-08-2012, 10:31 AM

OnErrorGoto0 and dangelor thanks to both of you! If Not Is Nothing line did the trick

Calculate sum and insert text in multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Re: Calculate sum and insert text in multiple sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions