Adding rows and columns in vba based on criteria

Question

i have some data for which i am trying to calculate the sum of every variable for 1-15th of every month and then the remaining days of month. The data is present in sheet named "data" and is like

Var 1-Jan   2-Jan   3-Jan   4-Jan   5-Jan   6-Jan   7-Jan   8-Jan   9-Jan   10-Jan  11-Jan  12-Jan  13-Jan  14-Jan  15-Jan  16-Jan...
var1    8   4   14  18  1   12  36  18  38  37  11  43  5   30  40  1
var2    29  39  40  10  12  21  27  31  10  21  5   14  14  33  17  13
var3    46  27  5   32  4   28  8   15  46  19  19  24  44  15  25  20
var4    5   46  27  21  10  19  26  34  38  38  33  16  23  2   26  8
var5    25  8   32  37  45  13  45  45  42  31  10  4   8   46  30  20

The data is for the whole year, but i am trying for jan-march duration. The output is in sheet "summary" and should be in the format

Var jan 1-15    jan 15-31   feb 1-15    feb 16-28   mar 1-15    mar 15-31
var1    sum sum sum sum...          
var2    sum...                  
var3                        
var4                        
var5

I was trying to write vba code like

Sub sum_data()

Dim flag1, flag2, k, sum
Dim arr As Variant
arr = Array(2, 15, 16, 15, 13, 15, 16)
flag1 = arr(0)

For i = 2 To 7
Sheets("data").Select
flag2 = flag1 + arr(i-1) - 1
For j = 2 To 5
sum = Application.sum(Range(Cells(j, flag1), Cells(j, flag2)))
Sheets("summary").Select
Cells(j, i) = sum
Next j
flag1 = flag2 + 1
Next i

End Sub

Please help me to find a better way to do it.

6}. arr is a zero based index. e.g. {0-6} not {1-9}

user4039065
– user4039065

2016-05-20 06:36:54 +00:00
Commented May 20, 2016 at 6:36 — user4039065
– user4039065, Commented May 20, 2016 at 6:36

ib11 · Accepted Answer · 2016-05-20 07:10:06Z

1

There is a mistake in the code where you select the "summary" sheet in the first sum, but then on the second time you want to sum you don't switch back to the other sheet.

This is a better approach without actually switching between sheets:

For i = 2 To 7
    flag2 = flag1 + arr(i - 1) - 1
    For j = 2 To 5
        sum = Application.sum(Sheets("data").Range(Cells(j, flag1), Cells(j, flag2)))
        Sheets("summary").Cells(j, i) = sum
    Next j
    flag1 = flag2 + 1
Next i

Hope this helps.

answered May 20, 2016 at 7:10

ib11

2,5584 gold badges26 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding rows and columns in vba based on criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related