Automatically dividing entered number by 12

Posted by Lori D./FL on August 10, 2001 5:08 PM

I would like whatever number I enter into a cell to be divided by 12, and I would like the cell to be empty and not show anything, so that when I go to it to enter the number, it will be blank, and then when I enter the number it will automatically be divided by 12. Can anyone help? TIA

Posted by Ivan F Moala on August 10, 2001 6:50 PM

One way to do this is via the application events.
Use the change event for the applications sheet.

eg.
Assumng the range to divide by 12 is A1:A10

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DivRg As Range

Set DivRg = Range("A1:A10")
Set DivRg = Application.Intersect(Target, DivRg)

If DivRg Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Target / 12
Application.EnableEvents = True

Set DivRg = Nothing

End Sub

Ivan

Posted by Lori on August 11, 2001 4:47 AM

Another question and need a little more help

Thanks for yor response Ivan. I don't have a range that needs to be divided. For example, whatever I enter in cell A12, I want automatically divided by 12. So if I enter $1,200.00 I want that divided by 12. How do I change your formula to do this? I just started using Excel three days ago so I'm still green about this - I actually wouldn't even know where to put your formula in, I really didn't think it would be so complicated. Thanks again.

One way to do this is via the application events.
Use the change event for the applications sheet.

eg.
Assumng the range to divide by 12 is A1:A10

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DivRg As Range

Set DivRg = Range("A1:A10")
Set DivRg = Application.Intersect(Target, DivRg)

If DivRg Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Target / 12
Application.EnableEvents = True

Set DivRg = Nothing

End Sub

Posted by Ivan F Moala on August 11, 2001 6:43 AM

Re: Another question and need a little more help

Lori
Sorry....should have explained further.
The routine below is a macro that is run when
(In this case) an event = Worksheet change
is triggered ie. when ever you change a cell
or range this event is initiated.
This particular event is @ the Worksheet(Tab)
level, so to enter this;
1) Right click your sheet tab that you want it
to run in.
2) select view code
3) paste it in.

Private Sub Worksheet_Change(ByVal Target As Range)
'Dim DivRg As Range

If Target.Address &LT;&GT; "$A$12" Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Target / 12
Application.EnableEvents = True

End Sub

Repost if further help needed

Ivan

Posted by Lori on August 11, 2001 7:04 AM

Re: Another question and need a little more help

Thanks for your quick response Ivan, I will give it a try and let you know how it works out!