'Add the number of words starting with particular letters until they are >=100, then start again with the next letter
I have a list of addresses for which I have the number of addresses that start with each letter of the alphabet. I would like to add A + B and see if it is less than or equal to 100. I don't want much more than 100 addresses per chunk if that makes sense. If A + B is >= 100, I want to put that value in a cell and then see if there are more than 100 records for "C" addresses. If there aren't, I'd like to add C + D and see if the number is >= 100, and so on. If A + B is less than 100, I need to add A + B + C and see if that is >=100.
This is a picture of the data
Here is the code that I have so far, but I think rather than copying this multiple times, there should be an easier way to tell Excel to step through the letters until the sum is >=100. I'm just not sure how to do it.
Dim sumAB As Integer
sumAB = Range("AF2").Value + Range("AF3").Value
Range("AI2").Value = sumAB
Range("AH2").Select
ActiveCell.Formula2R1C1 = "A-B"
If sumAB >= 100 Then
Range("AJ2").Value = sumAB
Else 'do nothing
End If
Dim sumABC As Integer
sumABC = Range("AF2").Value + Range("AF3").Value + Range("AF4").Value
Range("AI3").Value = sumABC
Range("AH3").Select
ActiveCell.Formula2R1C1 = " A-C"
If sumABC >= 100 Then
Range("AJ3").Value = sumABC
Else
End If
Solution 1:[1]
I figured it out using the following code:
For i = 2 To 27
alphasum = alphasum + Application.Cells(i, 32).Value
If alphasum >= 100 Then
Application.Cells(i, 35).Value = alphasum
alphasum = 0
Else 'do nothing
End If
Next i
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Christi |