Divisible by 3

[Juan Sanchez]

Marcy

The code as you posted it will only change the cells on
column A. If you input the value in I14 then it will not
change it, unless you change this part in the code:

Set rng = [A:A] 'Change this to reflect the cells that you
want to alter

To

Set rng = [I14:N36]

Make sure that you changed that bit of code and then try
again,

When you paste the code on the view code section of the
sheet (right click the sheet tab and so on) make sure
there are no red fonts. you should only see black, blue
and green.

Hope this helps, I pasted the code in a sheet and worked
fine.

Cheers
Juan

-----Original Message-----
Hi, all--

Ron Rosenfeld was kind enough to provide this wonderful

code to accomplish the task of making all numbers entered
in my named range (I14:N36) rounded up to the next number
divisible by 3.

The new question is, I have cut and pasted this into new

workbooks and it's not working, for some reason. I enter
the number 400 in cell I14 and NOTHIN'!! No change. It
was working before....

(fyi..I cut and paste from a file this was in that was

working just fine by right clicking on tab name and
selecting view code; then I pasted the code...isn't that
correct?)

And, perhaps more importantly, is there a way to make

this into a MACRO that I can assign a button and this
would execute?

Anxiously awaiting whatever advice you can offer.

=====================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range

Set rng = [A:A] 'Change this to reflect the cells that you want to alter

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.enableevents = False

For Each c In Target

If c.Value > 0 Then c.Value =

Application.WorksheetFunction.Ceiling(c.Value, 3)

If c.Value < 0 Then c.Value =

Application.WorksheetFunction.Floor(c.Value, -3)

 

[Juan Sanchez]

Marcy...

With minor changes to the code... yes it can.
Instead of pasting to the view code section of a
worksheet, paste into a module on the workbook or the
personal workbook so that it is avaylable on any worksheet.

If you don't have a personal macro book then do as follows:

1.Go to Tools > Macro > Record New Macro
2.On the Store Macro In: drop down, choose Personal Macro
Workbook.
3.Click OK
4.Click the Stop Button

Now you have a personal macro book.

Hit Alt+F11 and you will go to the VBA Editor, on the
project explorer you will find a: "VBAProject
(PERSONAL.XLS)" click on the + sign select the "Modules"
file and then go to Insert > Module

A new module appears and a white area on the righr is the
code area... there you will paste this:
====================================
Sub CloseTo3()
Dim rng As Range
Dim c As Range
Set rng = [A:A] 'Change this to reflect the cells that you
want to alter
For Each c In rng
If c.Value > 0 Then c.Value =
Application.WorksheetFunction.Ceiling(c.Value, 3)
If c.Value < 0 Then c.Value =
Application.WorksheetFunction.Floor(c.Value, -3)

Next c
End Sub
=============================================
Insert a new button on a tool bar by right clicking on any
tool bar and then choosing at the bottom "Customize", on
the "commands" tab scroll down to where it says "Macro",
choose the "Don't Worry Face Button" and drag it to the
tool bar were you wan't it. Then right click on the new
button and go to "Assign Macro" A list of macros will
apperar, choose the one that says: PERSONAL.XLS!CloseTo3
then click OK and the close the Customize Dialog Box and
there... your done!.

Hope this short tutorial helps...
Cheers
Juan

-----Original Message-----
Thanks for the reply, Juan.
However, I did change the range to I14:N36 when I pasted the code.
Since I wrote, I pasted it into another worksheet and it seems to be doing fine.

BUT...to the 2nd part of my question: Can this be

written so as to assign it to a button to use "as needed"?

Gratefully awaiting a reply.
--
Marcy

Marcy

The code as you posted it will only change the cells on
column A. If you input the value in I14 then it will not
change it, unless you change this part in the code:

Set rng = [A:A] 'Change this to reflect the cells that you
want to alter

To

Set rng = [I14:N36]

Make sure that you changed that bit of code and then try
again,

When you paste the code on the view code section of the
sheet (right click the sheet tab and so on) make sure
there are no red fonts. you should only see black, blue
and green.

Hope this helps, I pasted the code in a sheet and worked
fine.

Cheers
Juan

-----Original Message-----
Hi, all--

Ron Rosenfeld was kind enough to provide this

wonderful
code to accomplish the task of making all numbers entered
in my named range (I14:N36) rounded up to the next number
divisible by 3.

The new question is, I have cut and pasted this into

new
workbooks and it's not working, for some reason. I enter
the number 400 in cell I14 and NOTHIN'!! No change. It
was working before....

(fyi..I cut and paste from a file this was in that was

working just fine by right clicking on tab name and
selecting view code; then I pasted the code...isn't that
correct?)

And, perhaps more importantly, is there a way to make

this into a MACRO that I can assign a button and this
would execute?

Anxiously awaiting whatever advice you can offer.

=====================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range

Set rng = [A:A] 'Change this to reflect the cells that you want to alter

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.enableevents = False

For Each c In Target

If c.Value > 0 Then c.Value =

Application.WorksheetFunction.Ceiling(c.Value, 3)

If c.Value < 0 Then c.Value =

Application.WorksheetFunction.Floor(c.Value, -3)

Next c

Application.enableevents = True
End Sub
=======================

.