problem with udf modulus function

pete6982

New Member
Joined
Aug 7, 2006
Messages
19
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

Code:
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

Code:
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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Have you tried stepping through the code in break mode to see the calculation, and where it goes wrong?
 
Upvote 0
Another thought - do you have the same problem if you replace your "Modulus" function with an equivalent, like:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top