VBA 2010 Sum 2 single columns into 1 summary column for every column in the 2 ranges

[dhork]

Hi,


I come with a question involving excel VBA since I am no longer sure how I should proceed with this on Excel 2010.


Right now I have :

1. a summary table that is summed from 2 different single cell columns
2. they come from different month worksheet (12 in total)
3. currently, there are 2 ranges I need summed up to be displayed in the Summary sheet
3. they will stay in same cell range even on different worksheets as i use VBA to extract them to a set range of cells
4. a dropdown table with the months listed and a cell link to return the Month in Admin sheet

Sample of the cell range:
Company A (cell range = B7 to L40)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Current Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]COS[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Company B (cell range = P7 to Z40)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Current Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]1200[/TD]
[TD]3600[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]1200[/TD]
[TD]3600[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]COS[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]



My question is this:


1. Can i use an array or any other method other than hardcode everything for when the month changes via combobox to get the =SUM(January!B7+January!P7), =SUM(February!B7+February!P7) from a range of B7+P7 up until L40+Z40? This is a B7 + P7, B8 + P8, C7 + Q7 ..... L40 + Z40.


Something like

If Admin!C3 = 1 Then
            Arr = Sum(January!B7 + January!P7)


ElseIf Admin!C3 = 2 Then
            Arr = Sum(February!B7 + February!P7)


ElseIf Admin!C3 = 2 Then
            Arr = Sum(March!B7 + March!P7)

Code is incomplete because i don't really know how to proceed but is it possible for something along those lines? My programming is very rusty so I would very much appreciate it if someone could point me on the right track.

Many thanks in advance,
Dhork.

 

[Rijnsent]

Hi Dhork,

there are several solutions possible. For a starter, you could use a simple SUM formula... I created a workbook with 4 successive sheets, named JAN, FEB, MAR and APR, if I want to add up all C2 cells, this formula works: =SUM(JAN:APR!C2)
And for the other part of your question: the ADDRESS, OFFSET and INDIRECT formula come in handy there:
1: =SUM(January!B7+January!P7)
2: =SUM(February!B7+February!P7)

So if you have a little table with your month numbers and sheet names (month names) like so:
1 - January
2 - February
You could use a VLOOKUP to find the name of the month and then your formula would be something like
=SUM(INDIRECT(ADDRESS(ROW();COLUMN(B7);4;1;"JAN"));INDIRECT(ADDRESS(ROW();COLUMN(P7);4;1;"JAN")))
The "JAN" bit you'd have to replace with a VLOOKUP.

So no VBA needed whatsoever .

Hope that helps,

Koen

 

[dhork]

Hi Rjinsent,

I am trying to make each of the =SUM() cell to use the data from only 1 worksheet at a time and not to sum everything up from every month. It should sum each column for the 2 listed ranges but only for its own worksheet - January, February, March and April should be kept separate from each other. Unless this is what you mean and I have misunderstood your point :D

As for the second part which includes the VLOOKUP, you have my eternal gratitude for highlighting this to me. This simplifies matters to me to some extent.

Of course, I am still open to suggestions to the array VBA code that allows me to do this but I will start working on the VLOOKUP suggestion immediately.

Thanks!

Regards,
Dhork.

 

[Rijnsent]

Hi Dhork,

then indeed the INDIRECT will be your best solution. On the VBA part: what you could do is create a variable name based on your selected number and then refer to the stuff in your code like:

MnthNumber = Worksheets("Admin").range("A3").value
ShNm = Worksheets("Admin").range("B3").Offset(MnthNumber,0).value
SumArr = Worksheets(ShNm).Range("B7").value + Worksheets(ShtNm).Range("P7").value

Hope that code is of some use ,

Cheers,

Koen