problem with udf modulus function

[pete6982]

I am using excel 2000 and needed a mod function to return the mod number rather than 0. ex 3 mod 3 is normally 0, but I want it to return 3. So I wrote this function

Function Modulus(a, b) As Integer 'for x,y positive
    Do While a > b
        a = a - b
    Loop
    Modulus = a
End Function

it seems to work, but when I call it with this stmt

If Ceiling(i / 3) = Ceiling(x / 3) And Ceiling(j / 3) = Ceiling(y / 3) Then
    If Cells(x, y) <> 0 Then
        Possible((Modulus(x, 3) - 1) * 3+ Modulus(y, 3)) = 0
    End If
End If

with x=1,y=3; the y somehow becomes 0 when I try to set that member of possible to 0. I am stumped. any suggestions?

 

[dcardno]

Have you tried stepping through the code in break mode to see the calculation, and where it goes wrong?

 

[dcardno]

Another thought - do you have the same problem if you replace your "Modulus" function with an equivalent, like:

Function Modulus(x As Long, y As Long) As Long

Modulus = x Mod y - ((x Mod y) = 0) * y

End Function

Are the values supplied to the function declared as integers (or longs), or reals? If the "y" value supplied to the second Modulus function is a real, like say 3.005, then it will coerce 0.005 to an integer and return 0, instead of 3.