CODEWITHSUNDEEP
excelvbaloopssum
Kelvin C
Kelvin C

Reputation: 121

Excel VBA Loop sum up to a specific value

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.

enter image description here

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!

Upvotes: 0

Views: 948

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 1

Dominique
Dominique

Reputation: 17493

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.

Upvotes: 0

Related Questions