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!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Money (Existing Data)[/TD]
[TD]Project (To Be Populated)[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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.