Formula Needed!!!

[SteveR]

I need a formula for a wookbook. I want to limit or restrict the valu
of a single cell to any whole number multiple of 16. So far I have
been unable to come up with a workable formula. I would appreciate an
help I can get

 

[Jason Morin]

Although it has its limitations, you could use Data >
Validation > Custom and use the formula:

=AND(MOD(A1,16)=0,A1>0)

HTH
Jason
Atlanta, GA

 

[SteveR]

*Although it has its limitations, you could use Data >
Validation > Custom and use the formula:

=AND(MOD(A1,16)=0,A1>0)

HTH
Jason
Atlanta, GA

 

[SteveR]

I am having alittle trouble using this site and I apologize if I create
a double post. Thanks for the formula. I will try it tomorrow when
get to work. If any one else out there has a suggestion for me, I a
open to all

 

[Dave Peterson]

Maybe you could allow your user to type any non-negative(???) number in the cell
and you could have an event macro that would make sure it was rounded up to the
nearest multiple of 16.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
If IsNumeric(Target.Value) _
And IsEmpty(Target) = False _
And Target.Value >= 0 Then
Target.Value = Application.Ceiling(Target.Value, 16)
Else
Application.Undo
MsgBox "Please enter a number >= 0"
End If

errHandler:
Application.EnableEvents = True

End Sub

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this code in.

Change A1 to whatever range address you want.

 

[Ken Wright]

I believe Jason's method is how most would tackle it, but as per Jason's caveat,
you need to be aware that it is easily overcome by simply pasting data into the
cell.