Excel/VBA - Loop through range

Question

I have the following code:

TotalCount = Application.Sum(Worksheets("Data").Range("B14:G14"))
Sheets("Data").Range("H14") = TotalCount
TotalCount = Application.Sum(Worksheets("Data").Range("B13:G13"))
Sheets("Data").Range("H13") = TotalCount
TotalCount = Application.Sum(Worksheets("Data").Range("B12:G12"))
Sheets("Data").Range("H12") = TotalCount
TotalCount = Application.Sum(Worksheets("Data").Range("B11:G11"))
Sheets("Data").Range("H11") = TotalCount

As you can see... it's not very efficient. Problem is, i cant find a loop solution to make it better. When i try to create a loop i either get results i dont expect or it doesnt work. I'm at the point where i need some help... help! And thank you in advance.

Maybe if you can include an example of what you tried, somebody can point out what you are doing wrong. — martin
– martin, Commented May 21, 2015 at 15:39
I tried something like this... but no joy: For i = 3 To 11 TotalCount = Application.Sum(Worksheets("Data").Range("Z" & i: "AE" & i) Sheets("Data").Range("AF" & i) = TotalCount Next i — GtDriver
– GtDriver, Commented May 21, 2015 at 15:50
Then i thought i'd use 'Cells' instead of 'Range'... but i'm not sure how that'd hang together. — GtDriver
– GtDriver, Commented May 21, 2015 at 15:52

FreeMan · Accepted Answer · 2015-05-21 16:07:16Z

1

or, just for fun:

Dim i as integer
for i = 14 to 11 step -1
  Sheets("Data").Range("H" & i).Formula = "=SUM(B" & i & ":G" & i)
next

answered May 21, 2015 at 16:07

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

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

Comments

TMH8885 · Accepted Answer · 2015-05-21 15:53:02Z

0

For i = 1 to 4
    For t = 1 to 6
        TotalCount = Worksheets("Data").Cells(i+10,t+1).Value + TotalCount
    Next t
    Worksheets("Data").Cells(i+10,11).Value = TotalCount
    TotalCount = 0 'reset TotalCount
Next i

As long as you don't mind the macro going in the opposite direction, that should achieve what you're looking for.

answered May 21, 2015 at 15:53

TMH8885

8886 silver badges15 bronze badges

2 Comments

GtDriver Over a year ago

I tried this one and it suited my needs best -- thanks. I'm curious (and trying to learn). Why did you add a number after the looped letters (eg 'i + 10... and t+1). Why not just declare 'i = 11 to 14' and 't = 1 to 6'?

TMH8885 Over a year ago

I guess mostly out of habit. You could absolutely set it the way you described, I just like starting from 1. Maybe in my mind it makes changing the output position easier, or if you want to increment something while you loop it's easier. Comes down to preference.

Alex K. · Accepted Answer · 2015-05-21 15:50:29Z

0

This could be replaced with formulas in the H cells.

Or VBA:

Dim i As Long
Dim rngA As Range: Set rngA = Worksheets("Data").Range("B14:G14")
Dim rngB As Range: Set rngB = Worksheets("Data").Range("H14")

For i = 0 To 3
    rngB.Offset(-i, 0).Value = Application.Sum(rngA.Offset(-i, 0))
Next

You can also:

With Sheets("Data")
    .Range("H14") = Application.Sum(.Range("B14:G14"))
    ....
End With

answered May 21, 2015 at 15:50

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Comments

Gary's Student · Accepted Answer · 2015-05-21 15:53:26Z

0

We can use a Loop:

Sub UseLoop()
   With Sheets("Data")
   For i = 11 To 14
      TotalCount = Application.Sum(.Range("B" & i & ":G" & i))
      .Range("H" & i) = TotalCount
   Next i
   End With
End Sub

answered May 21, 2015 at 15:53

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Ioannis · Accepted Answer · 2015-05-21 16:17:08Z

0

You do not need a loop, just a bit of magic :)

Worksheets("Data").Range("H11:H14").Formula = "=B:B+C:C+D:D+E:E+F:F+G:G"

If you do not like the formula add

Range("H11:H14").Value2 = Range("H11:H14").Value2

edited May 21, 2015 at 16:17

answered May 21, 2015 at 16:06

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

4 Comments

FreeMan Over a year ago

Wouldn't that sum the entirety of columns B, C, D, E, F, & G and put the results in the 4 cells H11:H14?

Ioannis Over a year ago

It sums only rows 11:14 in columns B through G.

FreeMan Over a year ago

Interesting! I just tried this out in a new worksheet - I learn something new every day. I saw B:B as a full column reference - I didn't know that leaving the row out caused it to use the current row.

Ioannis Over a year ago

Yes, Excel implicitly considers the same subset of rows as the one in the left hand side expression. It is cool :)

Jim Becker · Accepted Answer · 2015-05-22 23:38:27Z

0

Range("H11:H14").Formula = "=SUM(B11:G11)"

answered May 22, 2015 at 23:38

Jim Becker

2091 silver badge5 bronze badges

Collectives™ on Stack Overflow

Excel/VBA - Loop through range

6 Answers 6

Comments

2 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related