Largest sum of two values in wksht collection


M

Mike Taylor

Can someone offer help with code that will programmaticaly loop thru a
worksheet collection in a workbook, determine the largest sum of two
cells within a monthly range, and summarize those results in a summary
worksheet? The following is provided in the hope it clarifies what I
am trying to achieve.

Situation:
A workbook contains 116 data worksheets each of which are identical
except for the values of their cells. Worksheet names are determined
by value of cell A1 in each respective worksheet (format "000"). A
simplified example of the first worksheeet named "001" is as follows:

A B C D E ... G
1 001
2 1/1/04 Olgivy $900 $800 more...data
3 1/8/04 Larson $300 $100 more...data
4 2/2/04 Guillett $700 $600 more...data
5 2/7/04 Peterson $800 $400 more...data
6 3/6/04 Kabel $500 $300 more...data
7 3/9/04 de Bruin $400 $200 more...data

I place a summary worksheet named "Consolidation" after worksheet 116
and use it to derive various sums of values from the collection of 116
worksheets.

Question:
How would one programatically loop through all 116 data worksheets
beginning with the first worksheet, determine the largest sum of range
Cx:Dx (where x=row number) that occured during each month (number of
rows per month varies from month to month) and list those results for
each worksheet within range N1:Z116 of the Consolidation worksheet to
achieve the following? Column N lists name of worksheet, and Columns
O:Z are used for monthly largest sum of Cx:Dx.

N O P Q R S ... Z
1 1/04 2/04 3/04 4/04 5/04 12/04
2 001 $1700 $1300 $800
3 002 (loop thru all 116 worksheets and repeat for each sheet)
4 003

I have been unable to locate any prior posts that illustrate methods
to determine the largest sum of two cells in a range and where the
range limits vary according to dates. Harder to explain than I thought
it would be...hope someone can weigh in with possibilites.

TIA for any response...

Mike Taylor
 
Ad

Advertisements

S

Sharad Naik

Can you explain what to you mean by "Largest sum of range Cx:Dx' ?
Do you mean add C2+C3+C4..... + D2+D3+D4.... for each sheet,
and out of 116 sheets, the which is largest, to be identified ?

Sharad
 
Ad

Advertisements

T

Tom Ogilvy

application.Evaluate("Max(if(Month(A2:A30)=2,C1:C30+D1:D30,0))")

Should give you the largest sum for a particular month.

You would just need to write code to generate the correct formula.
(basically, build the formula string, then pass it to evaluate).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top