Cell input to automatically divide itself

[Guest]

Hi. I would like to know if it is possible to get the input of a cell to
automatically divide itself. So, if someone enters 4 in to A1, I would like
it to be divided by 2 and show the value 2 in the same input sell (A1).

Can this be done, and if it can, how?

Thanks in advance
Jas

 

[Guest]

Use the worksheet_change event.

An overview of events
http://www.cpearson.com/excel/events.htm

 

[Guest]

hi,

Right click the sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = (Target.Value / 2)
Application.EnableEvents = True
End If
End If
End Sub


Mike

 

[Guest]

Thats brilliant - thank you so much - it works a treat!!!

 

[Guest]

You can do this without using macros by doing a little bit of slight of hand...

Assuming you are dealing with cell A1 on Sheet1... For Sheet 1 A1 make the
font colour the same as the background colour. Add the formula =Sheet1!A1/2
in cell A1 on Sheet 2. Select Tools -> Options -> View and uncheck Gridlines.
Now follow the oddity outlined here...

http://j-walk.com/ss/excel/odd/odd04.htm

I like this method because you do not need to enable macros and the original
value entered by the user is there in the cell (shows in the formula bar when
the cell is selected).

 

[Don Guillett]

right click sheet tab>view code>copy/paste this
As written, it works ONLY on column D

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
Target = Target / 2
Application.EnableEvents = True
End Sub

 

[Guest]

Just a heads up to the OP.
Since this doesn't check for errors and doesn't handle errors and attempts
to perform a math operation regardless of the value in the cell it would be
a poor implementation. Mike H does account for those problem.s

 

[Don Guillett]

But it would do what the OP asked for.....

 

[Guest]

Since you are an MVP and you posted it well after a much superior solution
was posted, I think it is fair to advise the OP that it was ill conceived.
If he implements your solution, then gets an error and events are disabled
and he wonders what happened and so forth.

And Yes, at the lowest level, it answered his question - I don't think I
said otherwise.

 

[Don Guillett]

Tom, As always you are correct. However, OP did not ask for more.