selective addition, help getting started

I have a problem. I have an undefined number of products (these are mutual funds) with an undefined number of rows for each product. For each product a report has to be generated. Please view file. The colors simplify things.

Of course, these reports can be generated manually. However, this isn't the most efficient way of doing things.

Can anyone help get me started? What code structures would I use to do something like this? I was thinking I should use some type of loop.

This was done before in VB. Could this be done any easier or just as easily in Excel 2003 with VBA?

Thank you in advanced for any suggestions.




Please post Excel questions, in the Excel forum.

Thank you.

Hi jcavigli and welcome to the forum :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

Let's try to avoid VBA for now and explore the native Excel solution:

In Excel if you want to unconditionally sum numbers then you use the SUM function.
If you want to SUM based on 1 single condition then you use the SUMIF function.

Here you want to SUM the market cap percentage based on 2 conditions: the product name and the market cap. In versions of Excel prior to 2007, as soon as you start wanting to sum based on multiple conditions you should start to think about the Sumproduct or DSum functions. Other options might be an array formula or a pivottable.

An example of using Sumproduct to generate the result in F18 would be this:

=SUMPRODUCT(-(A2:A58=$F$17),-(B2:B58>50000000000),(C2:C58))*100


So I guess that puts the ball back in your court and you need to decide if this suggestions suits you. If you want to understand more about how these types of formula work then we do have a tutorial on the subject:

http://www.xtremevbtalk.com/showthread.php?t=296012

I think the dsum function might do the trick. However, this function requires you to specify the range. I do not know the range nor the name of each product. If I open the file I may have two products called mutual fund a with 10 rows and mutual fund b with 5 rows. Or I might have 3 products called hedge a with 100 rows, hedge b with 200 rows, and hedge c with 1000 rows. I just don't know.

What code structure would you recomend for ascertaining the range of each product?

You could use dynamic named ranges. Making a specific suggestion would require seeing the data layout.

Just for xvbt members' reference, this question has been cross-posted here:

http://www.vbforums.com/showthread.php?t=521639