Excel VBA Loop sum up to a specific value

Question

Haven't had any luck trying to find an answer to the following and the code I've come up with is just horrible.

I'm trying to select a range of cells that sum to a specific value.

Column A are Room Numbers Column B is time in minutes spent to clean each room.

I'm looking for a way to loop through column B until their summed value is = or < 200

And then select the corresponding Rooms in Column A (Which will then be move to another sheet)

Does anyone have a simple way of achieving this?

Thanks very much!

Please add the code you've tried so far and people here will help you modify it to work properly. — Super Symmetry
– Super Symmetry, Commented Aug 6, 2021 at 6:15

FaneDuru · Accepted Answer · 2021-08-06 08:21:22Z

1

Please, try the next code:

Sub testSumLimit()
   Dim ws As Worksheet, lastR As Long, sumLimit As Long, rng As Range, i As Long, count As Long
   
   Set ws = ActiveSheet
   lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
   sumLimit = 200: ws.Range("A1").Activate
   For i = 2 To lastR
        count = count + ws.Range("B" & i).value
        If count >= sumLimit Then
            Set rng = ws.Range("A2:B" & i): Exit For
        End If
   Next i
   rng.Select
End Sub

answered Aug 6, 2021 at 8:21

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

2 Comments

Kelvin C Over a year ago

Thanks FaneDuru! That worked perfectly, very much appreciated!

FaneDuru Over a year ago

@Kelvin C Glad I could help! But, don't you need doing something with the respective range? Only selecting it? Basically, selecting ranges or activating sheets is a bad habit, which only consumes Excel resources...

Dominique · Accepted Answer · 2021-08-06 06:10:14Z

0

It makes no sense to loop until the sum is smaller than 200, because that will make your loop stop already at the first step.

In order to achieve what you want, you need to loop until the sum is larger than 200, and then subtract the last entry.

answered Aug 6, 2021 at 6:10

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Collectives™ on Stack Overflow

Excel VBA Loop sum up to a specific value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related