CODEWITHSUNDEEP
vbaexcelautofill
Paul McLain
Paul McLain

Reputation: 359

Increment Autofill Range by Variable Number

A requirement for a work project is to sum hourly values together by day for a certain number of categories. These values are stored in a spreadsheet in one long column; to get the values, I sum every 24 rows into a new day. This can be accomplished by looping through the spreadsheet and increasing the values for each loop by 24.

For example:

first = 1
second = 24
i = 0
While Worksheets("Data").Range("A1").offset(i, 0) <> ""
    Worksheets("Data").Range("C1").offset(i, 0).Formula = "=SUM(B" & first & ":B" & second & ")")
    first = first + 24
    second = second + 24
Wend

Where column A is the category and column B is the hourly data. Column B will be the length of column A * 24.

This produces results like...

=SUM(B1:B24)
=SUM(B25:B48)
=SUM(B49:B72)

.. and so on.

This works well, but is very slow. What I would like to do is use AutoFill for the entire range, but I'm not sure how to write it in such a way that it will increment the values by 25 each time. So far I have only been able to get it to increment by one for each value, where 

=SUM(B1:B24)

becomes

=SUM(B2:B25)

Is there a way to use AutoFill on a range where it will increment by a variable number -- in this case, 24?

Thanks in advance.

Upvotes: 1

Views: 1349

Answers (2)

Tiago Cardoso
Tiago Cardoso

Reputation: 2107

There might be plenty of ways to do it, both using or not VBA.

I present you a solution using only Excel, but can be migrated to VBA to avoid other column's usage (I believe there must be a way to do it without using support columns, someone else here in the community might remember how, eventually).

Well, the trick here is use the INDIRECT formula.

I'll assume you're applying the sum formula into column C.

In columns D and E, you will store the row indexes. Then, we'll have:

|D      |E      |
|-------|-------|
|1      |24     |
|=D1+24 |=E1+24 |
|=D2+24 |=E2+24 |

Now, in column C, instead of using Sum with direct values, use:

=SUM(INDIRECT("B"&D1):INDIRECT("B"&E1))
=SUM(INDIRECT("B"&D2):INDIRECT("B"&E2))
=SUM(INDIRECT("B"&D3):INDIRECT("B"&E3))

This code can be dragged down as much as you want, as long as you drag the columns C, D and E together.

Again, it could also be done in a fancier way using VBA (and maybe in a smarter way using only Excel, hehe) but that's the way that came up to my mind. Anyway, most of the questions I've seen here about simple VBA implementations were for personal usage, so use other columns wouldn't be a problem.

In case you prefer (or need) to use VBA, draft your code and we'll be helping you out.

Hope it helps.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166825

If your only complaint with your current approach is its speed, then you can probably make it significantly faster by suspending calculation while you insert the formula:

Application.Calculation = xlCalculationManual
'do your thing
Application.Calculation = xlCalculationAutomatic

Upvotes: 1

Related Questions