Mod With no Zeroes

I use both VBA’s and Excel’s Mod function occasionally, and many times I’ve wanted them to behave a little differently.

As described at its web page, the Mod function:

Returns the remainder after number is divided by divisor.

Mod is useful when you want to cycle through a group assigning repeating values to its members. It’s like when you go to a work training and have to count off by fours so you can break up into small groups and brainstorm or, preferably, when you go to the beach and have to come up with capture-the-flag teams. Here’s how it might look in Excel.

The thing is that the MOD function won’t give you quite that result, at least not without a little tweaking. Because it returns the remainder, the cycle always starts at one and ends at zero. So at your meeting it’s as if you asked people to count off like:

The solution is pretty simple (though it took me a while to figure out). Subtract one from the number you’re MODding, MOD it, and add one to the result. For example, in the worksheet above the formula in the Group column is:

=MOD(ROW()-2,4)+1

(We’re subtracting 2 because the names start in row 2)

Here’s a simple procedure in VBA to process that same list, using the same manipulation of MOD:

Sub GroupAttendees()
Dim ws As Excel.Worksheet
Dim i As Long
Dim Attendees As Variant
Dim GroupCount As Long

Set ws = ActiveSheet
Attendees = Application.Transpose(ws.ListObjects(1).ListColumns("Name").DataBodyRange)
GroupCount = 4
For i = LBound(Attendees) To UBound(Attendees)
Debug.Print Attendees(i); " "; ((i - 1) Mod GroupCount) + 1
Next i
End Sub

Here’s the results in the immediate window:

For a thorough discussion of Mod, including some gotchas, this page looks interesting.

2 thoughts on “Mod With no Zeroes”