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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
558
Office Version
  1. 2013
Platform
  1. Windows
Have you tried stepping through the code in break mode to see the calculation, and where it goes wrong?
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
558
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,126
Messages
5,901,539
Members
434,899
Latest member
powerappsjoker99

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
Top