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

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

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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Jas" <(E-Mail Removed)> wrote in message
news:7E0BE0A2-56D0-4EAE-9EF8-(E-Mail Removed)...
> 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

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 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
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Jas" <(E-Mail Removed)> wrote in message
> news:7E0BE0A2-56D0-4EAE-9EF8-(E-Mail Removed)...
> > 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
>
>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:FAC035FE-1689-4194-8784-(E-Mail Removed)...
> 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 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
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Jas" <(E-Mail Removed)> wrote in message
>> news:7E0BE0A2-56D0-4EAE-9EF8-(E-Mail Removed)...
>> > 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
>>
>>

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
> (E-Mail Removed)
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:FAC035FE-1689-4194-8784-(E-Mail Removed)...
> > 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 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
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Jas" <(E-Mail Removed)> wrote in message
> >> news:7E0BE0A2-56D0-4EAE-9EF8-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:80D1876B-9E0C-4DBD-B2B8-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
>> news:FAC035FE-1689-4194-8784-(E-Mail Removed)...
>> > 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 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
>> >>
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Jas" <(E-Mail Removed)> wrote in message
>> >> news:7E0BE0A2-56D0-4EAE-9EF8-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>>
>>