Macro to Sum Multiple Times in Column Based on Variable Range

**kareni** · 03-15-2011, 06:06 PM

I'm trying to prepare a macro that will run the sum formula for a column based on variable range. The sum formulas need to repeat in Column K everytime the 2011 is shown. See data below:

Column K
SCURNAMT
72
238.43
884.08
87.3
2011
67.99
2011
10.24
50.58
46.09
2011

This is what I have come up with, which works but gives me an error because the object is variable. Can anyone tell me how to fix the following macro please?

'Sum amount of each transaction

Please Login or Register  to view this content.

**MarvinP** · 03-15-2011, 06:35 PM

Hi kareni and welcome to the forum,

You need to do something before I can help you.
Click on the Green Edit button below your post and then click on "Go Advanced" under the message box. Then select the code in your message area and click on the "#" Icon above the message area. This will put Code Tags around your code. Then save the changes.

If you don't do this and I answer your question the Forum Moderators will give me a demerit!!

You follow the darn rules and we can help.

**kareni** · 03-22-2011, 01:16 PM

Anyone have suggestions on how to fix the macro? The macro works when I use it but gives me an error so it just needs to be refined.

**MarvinP** · 03-22-2011, 01:42 PM

HI karen.
Try this - if I understand what you are trying to do. And find the attached with a sample

Please Login or Register  to view this content.

**kareni** · 03-22-2011, 01:49 PM

Can you re-attach the file in xls form please? Sorry, I only have the 2003 version at work.

**MarvinP** · 03-22-2011, 01:51 PM

My Bad - I normally look and see - I must be getting old

**kareni** · 03-22-2011, 02:32 PM

Hmm, it's not working when I put the macro in my working file. I'm not sure why or if I copied the macro within the file incorrectly.

I've attached a similar copy of file I'm currently using so you can see how the file and macros are setup.

**MarvinP** · 03-22-2011, 03:46 PM

Hi karen,

It worked for me. See the attached. I'm not sure you have macros figured out yet?
My macro was not in what you sent. I also had to remove a partial macro on a sheet.

**kareni** · 03-22-2011, 05:30 PM

Thanks Marvin! I really appreciate all of your help. I've been working on this for weeks before I finally decided to ask for help. I really don't know macros at all and was merely trying to fix an existing worksheet that someone had created years ago.

For the most part, I edited what you entered and gotten it to work with the one button, except that it calculates the last batch of transactions incorrectly. The sum is off by 100 for some reason. If you open the attached spreadsheet, click run macro, and enter 2007 into the box, it should run all the macros at the same time (including yours). When all the numbers are highlighted in column K, it should equal zero.

**MarvinP** · 03-22-2011, 06:21 PM

Hi karen,
How about not using macros? Look at the Pivot Table.
I was somewhat confused on your LastRow calculation. I felt is might be one short.
See attached - Pivots are very easy to make.

**MarvinP** · 03-22-2011, 07:00 PM

The reason K675 is -704.44 instead of -804.44 is you don't calculate that last row. Your row counter is one short and -704.44 was in that cell from the beginning.

I think it started when you did a Count(A2:A10000). You started at 2 and counted all rows that had stuff in it. This left you one row short.

This is a VERY COMMON problem that is guarded against with code like:

Please Login or Register  to view this content.

which would give the last row number of data in row A.

Count counts the number of cells that are numbers. What if one of the values in column A had a letter in it? It would be one short.

In the olden days in the testing world we called this the One Off problem.

**kareni** · 03-22-2011, 07:05 PM

Oh ok, got it. I also just realized that when the data is imported, the last row doesn't have a 2011 in it so I think that contributed to the error.

Thank you so much for your help! You totally made my day!

**kareni** · 03-22-2011, 08:15 PM

Hi, just fyi.

I was able to fix the error for the last row by changing the macro a little bit to use the 2007 value in column F as a reference to sum up the numbers in column K which eliminated the problem of the last row not being included when the macro was summing up the values. Again, thanks so much for your help.

Please Login or Register  to view this content.

Macro to Sum Multiple Times in Column Based on Variable Range

LinkBack

Thread Tools

Rate This Thread

Display

Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Re: Macro to Sum Multiple Times in Column Based on Variable Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions