Cell input to automatically divide itself
 

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

Cell input to automatically divide itself
 

Use the worksheet_change event.

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

--
Regards,
Tom Ogilvy


"Jas" wrote:

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

Cell input to automatically divide itself
 

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

"Jas" wrote:

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

Cell input to automatically divide itself
 

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

"Mike H" wrote:

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

"Jas" wrote:

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

Cell input to automatically divide itself
 

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).
--
HTH...

Jim Thomlinson


"Jas" wrote:

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

Cell input to automatically divide itself
 

right click sheet tabview codecopy/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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jas" wrote in message
...
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

Cell input to automatically divide itself
 

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

--
Regards,
Tom Ogilvy



"Don Guillett" wrote:

right click sheet tabview codecopy/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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jas" wrote in message
...
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

Cell input to automatically divide itself
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Don Guillett" wrote:

right click sheet tabview codecopy/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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jas" wrote in message
...
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

Cell input to automatically divide itself
 

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.


--
Regards,
Tom Ogilvy




"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Don Guillett" wrote:

right click sheet tabview codecopy/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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jas" wrote in message
...
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

Cell input to automatically divide itself
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy




"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Don Guillett" wrote:

right click sheet tabview codecopy/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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jas" wrote in message
...
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