Fluid (moving) sum formula

[JayCheezey]

Hi everyone!

New coder here, I'm just wondering if I could get some help on some simple code.

I'm trying to create a macro that allows me to continuously get the sum of multiple weeks. Each time I run the macro, it adds one additional week. This is the tricky part as I'm not sure what I function I would use to continuously expand a sum formula.

For example, I run the macro, it sums W1 to W2 (=5). The next time I run the macro, it would sum W1 to W3 (=8). And after that sum W1 to W4 (=10)


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]W1[/TD]
[TD="align: center"]W2[/TD]
[TD="align: center"]W3
[/TD]
[TD="align: center"]W4[/TD]
[TD="align: center"]W5[/TD]
[TD="align: center"]W6[/TD]
[TD="align: center"]W7[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]


Thanks a lot in advance. This function is the last part of a large piece of code I'm trying to get done.

Much appreciated!

 

[NeonRedSharpie]

Where is the sum value being stored? Is it always being stored in the same cell?

 

[JayCheezey]

Where is the sum value being stored? Is it always being stored in the same cell?

Hey, thanks for the quick reply.

The sum is indeed stored in the same cell.

 

[NeonRedSharpie]

Is it stored as a formula or as a static value?

 

[Matty]

Why not set things up like this:

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]6[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]20[/TD]

</tbody>

Where I1 contains the week you want to go to, and then use this formula in I2:

=SUM(A2:INDEX(A2:G2,I1))

Matty

 

[JayCheezey]

Is it stored as a formula or as a static value?

Not sure if this is answering your question, but the value of the summed cell ideally should display the formula.

 

[JayCheezey]

Why not set things up like this:

	A	B	C	D	E	F	G	H	I

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]W7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]6[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: center"]20[/TD]

</tbody>

Where I1 contains the week you want to go to, and then use this formula in I2:

=SUM(A2:INDEX(A2:G2,I1))

Matty

Maybe I should give a better description of what I'm trying to accomplish.

Essentially, what I'm trying to do is code in vba.

The situation is I have last years set of numbers. (W1-W52)

As this year progresses, I want to compare this year's W1-W30 with last year's W1-W30.

So next week, when I receive the value for W31, I want the sum for W1-W31 this year, and W1-W31 of last year. I have the coding for this year done. But I'm note entirely sure how to code W1 - W31 of last year.

accomplished this years code by using W1 as a absolute value, and then ".end (xlToRight)" to get the sum from W1-W30 of this year. Unfortunately I can't do this for last years number because the format is like this...

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD][/TD]
[TD]Year to Date[/TD]
[TD]Last Year to date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD][/TD]
[TD]Sum of 2014[/TD]
[TD]sum of 2013[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]


In this example, I'm comparing W1-2 of last year with W1-2 of this year.

Next week, when I get numbers for W3, I want the macro to generate the sum of W1-3 this year and W1-3 last year.

I've got the code for this year...but I can't seem to produce a code to get the sums of last year to move 1 over.


Hope this clears things up!


Thanks!

 

[Matty]

Why do you need to use VBA when standard formulas will deliver what you need? See below:

	A	B	C	D	E	F	G	H	I	J	K

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]2013[/TD]
[TD="bgcolor: #FAFAFA"]2013[/TD]
[TD="bgcolor: #FAFAFA"]2013[/TD]
[TD="bgcolor: #FAFAFA"]2013[/TD]
[TD="bgcolor: #FAFAFA"]2014[/TD]
[TD="bgcolor: #FAFAFA"]2014[/TD]
[TD="bgcolor: #FAFAFA"]2014[/TD]
[TD="bgcolor: #FAFAFA"]2014[/TD]
[TD="bgcolor: #FAFAFA"]Year to Date[/TD]
[TD="bgcolor: #FAFAFA"]Last Year to date[/TD]
[TD="bgcolor: #FAFAFA"]Current Week[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]W1[/TD]
[TD="bgcolor: #FAFAFA"]W2[/TD]
[TD="bgcolor: #FAFAFA"]W3[/TD]
[TD="bgcolor: #FAFAFA"]W4[/TD]
[TD="bgcolor: #FAFAFA"]W1[/TD]
[TD="bgcolor: #FAFAFA"]W2[/TD]
[TD="bgcolor: #FAFAFA"]W3[/TD]
[TD="bgcolor: #FAFAFA"]W4[/TD]
[TD="bgcolor: #FAFAFA"]Sum of 2014[/TD]
[TD="bgcolor: #FAFAFA"]sum of 2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA"]-[/TD]
[TD="bgcolor: #FAFAFA"]-[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Formulas as follows:

I3:

=SUM(IF(A1:H1="2014",IF(--SUBSTITUTE(A2:H2,"W","")<=K2,A3:H3)))

J3:

=SUM(IF(A1:H1="2013",IF(--SUBSTITUTE(A2:H2,"W","")<=K2,A3:H3)))

Both of which need entering with CTRL+SHIFT+ENTER.

Matty

 

[Matty]

Hi,

In response to your PM...

If you're trying to create some kind of report, I would strongly suggest keeping data and results completely separate. This will then keep things very flexible if you want to carry out additional comparisons at a later stage.

If you need to retain the format you've described and want to pursue the VBA option, reply back with what you're wanting to do and hopefully one of the coders on here will be able to help.

Matty

 

[JayCheezey]

Thanks for the advice!

Hi,

In response to your PM...

If you're trying to create some kind of report, I would strongly suggest keeping data and results completely separate. This will then keep things very flexible if you want to carry out additional comparisons at a later stage.

If you need to retain the format you've described and want to pursue the VBA option, reply back with what you're wanting to do and hopefully one of the coders on here will be able to help.

Matty