VBA Loop Help Needed

[jonchimento]

Hello,

I have a series of costs and I want to group them into X dollar groups (In the example below its 300). I have a much larger set of data then shown below and want to create an automated way of doing this because I will have to repeat the process with new data in the future.
I am trying to use VBA loop and am not really getting anywhere.
Hope someone out there can help!

Money (Existing Data)	Project (To Be Populated)
100	1
200	1
250	2
50	2
80	3
150	3
110	3
250	4
90	4

<tbody>
</tbody>

Below in my attempt at creating VBA code to do this:

Sub Projects()

x = 300
Range("AX2").Select ActiveCell.FormulaR1C1 = "1"
Do Until Application.SumIf(Range("$B:$B"), 1, Range("$A:$A")) > x
ActiveCell.Offset(1, 0).FormulaR1C1 = "1"
Loop
Do Until Application.SumIf(Range("$B:$B"), 2, Range("$A:$A")) > x
ActiveCell.Offset(1, 0).FormulaR1C1 = "2"
Loop

End Sub

 

[bbott]

Welcome to the board.

Does it need to be VBA? If not, and assuming your table begins in A1, try this formula in B2 and copy down:

=INT(SUM($A$2:A2)/301)+1

 

[jonchimento]

Welcome to the board.

Does it need to be VBA? If not, and assuming your table begins in A1, try this formula in B1 and copy down:

=INT(SUM($A$2:A2)/301)+1

Worked beautifully! Thank you!!!

 

[bbott]

Worked beautifully! Thank you!!!

You're welcome. One small issue to that formula though. If you have any values above 300, it won't quite work correctly...Just so you're aware.

 

[jonchimento]

It worked how I needed, it gave me groups of around my goal number. The groups were below and above the target number with 15% variation, no problem there.