This works for me putting it into the sheet's module.
Waves 1,2, 3 and 5 will round up and Wave 4, the largest group, will be reduced so total number of computers will be correct.
Code:
Sub AssignWaveGroup()
Dim TotalComputers As Double
Dim Wave(0 To 4) As Double
Dim startWave As Long
Dim endWave As Long
Dim i As Integer
TotalComputers = Cells(Rows.Count, "A").End(xlUp).Row - 1
With Application.WorksheetFunction
Wave(0) = .RoundUp(TotalComputers * 0.02, 0)
Wave(1) = .RoundUp(TotalComputers * 0.03, 0)
Wave(2) = .RoundUp(TotalComputers * 0.25, 0)
Wave(4) = .RoundUp(TotalComputers * 0.25, 0)
Wave(3) = TotalComputers - Wave(0) - Wave(1) - Wave(2) - Wave(4)
End With
For i = 0 To 4
startWave = Cells(Rows.Count, "B").End(xlUp).Row + 1
endWave = startWave - 1 + Wave(i)
Range("B" & startWave & ":B" & endWave).Value = i + 1
Next i
End Sub
Bookmarks