circular error
 

I think this is a stupid question but I'm going to ask anyway :-) Actually
it's on behalf of a friend of mine and I've told him it's not possible but I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display that
number multiplied by a given number in that same cell? I don't think this is
possible because of a circular error. I don't even know why he would want to
achieve this, he hasn't told me. Is there way of doing this at all - maybe
using VBA?

circular error
 

You can have an accumulator cell but only if you use another cell for input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-) Actually
it's on behalf of a friend of mine and I've told him it's not possible but I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display that
number multiplied by a given number in that same cell? I don't think this is
possible because of a circular error. I don't even know why he would want to
achieve this, he hasn't told me. Is there way of doing this at all - maybe
using VBA?

circular error
 

Hi Gord,
Many thanks for your fast reply - it really is appreciated. I'll give this a
go tomorrow.


Best wishes,


Gavin



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think this
is
possible because of a circular error. I don't even know why he would want
to
achieve this, he hasn't told me. Is there way of doing this at all - maybe
using VBA?

circular error
 

Hi Gord,
I tried the code this morning at work but I got an error. I have uploaded
the error screens to Flickr he

http://www.flickr.com/photos/13882468@N04/2232610943/

http://www.flickr.com/photos/13882468@N04/2232611043/

Can you determine from those what the problem is? Have I done something
wrong?


Regards,



Gavin





"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think this
is
possible because of a circular error. I don't even know why he would want
to
achieve this, he hasn't told me. Is there way of doing this at all - maybe
using VBA?

circular error
 

Error1 is caused by not including the "" in

If Target.Cells.Count 1 Then Exit Sub

Error2 is exactly the same error.

See my original code which did not have the "" missing.

Best to check other lines also just in case you missed something else in the
copy over.


Gord


On Thu, 31 Jan 2008 19:23:06 GMT, "gavin"
wrote:

Hi Gord,
I tried the code this morning at work but I got an error. I have uploaded
the error screens to Flickr he

http://www.flickr.com/photos/13882468@N04/2232610943/

http://www.flickr.com/photos/13882468@N04/2232611043/

Can you determine from those what the problem is? Have I done something
wrong?


Regards,



Gavin





"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think this
is
possible because of a circular error. I don't even know why he would want
to
achieve this, he hasn't told me. Is there way of doing this at all - maybe
using VBA?

circular error
 

Thanks again, Gord - I knew I must have done something really stupid!


Regards


Gavin


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Error1 is caused by not including the "" in

If Target.Cells.Count 1 Then Exit Sub

Error2 is exactly the same error.

See my original code which did not have the "" missing.

Best to check other lines also just in case you missed something else in
the
copy over.


Gord


On Thu, 31 Jan 2008 19:23:06 GMT, "gavin"
wrote:

Hi Gord,
I tried the code this morning at work but I got an error. I have uploaded
the error screens to Flickr he

http://www.flickr.com/photos/13882468@N04/2232610943/

http://www.flickr.com/photos/13882468@N04/2232611043/

Can you determine from those what the problem is? Have I done something
wrong?


Regards,



Gavin





"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible
but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think
this
is
possible because of a circular error. I don't even know why he would
want
to
achieve this, he hasn't told me. Is there way of doing this at all -
maybe
using VBA?

circular error
 

Works a treat - many thanks, Gord.


Gavin




"gavin" wrote in message
.. .
Thanks again, Gord - I knew I must have done something really stupid!


Regards


Gavin


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Error1 is caused by not including the "" in

If Target.Cells.Count 1 Then Exit Sub

Error2 is exactly the same error.

See my original code which did not have the "" missing.

Best to check other lines also just in case you missed something else in
the
copy over.


Gord


On Thu, 31 Jan 2008 19:23:06 GMT, "gavin"
wrote:

Hi Gord,
I tried the code this morning at work but I got an error. I have uploaded
the error screens to Flickr he

http://www.flickr.com/photos/13882468@N04/2232610943/

http://www.flickr.com/photos/13882468@N04/2232611043/

Can you determine from those what the problem is? Have I done something
wrong?


Regards,



Gavin





"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible
but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think
this
is
possible because of a circular error. I don't even know why he would
want
to
achieve this, he hasn't told me. Is there way of doing this at all -
maybe
using VBA?

circular error
 

Happens to me the odd time when I copy from a reply to a posting.

The lines are prefaced with the (see below)

After pasting I do a general find/replace for and lose the internal also

Got to watch for that.


Gord

On Fri, 01 Feb 2008 18:40:15 GMT, "gavin"
wrote:

Thanks again, Gord - I knew I must have done something really stupid!


Regards


Gavin


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Error1 is caused by not including the "" in

If Target.Cells.Count 1 Then Exit Sub

Error2 is exactly the same error.

See my original code which did not have the "" missing.

Best to check other lines also just in case you missed something else in
the
copy over.


Gord


On Thu, 31 Jan 2008 19:23:06 GMT, "gavin"
wrote:

Hi Gord,
I tried the code this morning at work but I got an error. I have uploaded
the error screens to Flickr he

http://www.flickr.com/photos/13882468@N04/2232610943/

http://www.flickr.com/photos/13882468@N04/2232611043/

Can you determine from those what the problem is? Have I done something
wrong?


Regards,



Gavin





"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can have an accumulator cell but only if you use another cell for
input and
only when adding, not multiplying.

For multiplying you would need VBA event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Value = .Value * 23 'fixed given number
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into
that
sheet module.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 20:12:30 GMT, "gavin"
wrote:

I think this is a stupid question but I'm going to ask anyway :-)
Actually
it's on behalf of a friend of mine and I've told him it's not possible
but
I
thought I would check with the experts!

Is it possible to enter a number into a cell and have the cell display
that
number multiplied by a given number in that same cell? I don't think
this
is
possible because of a circular error. I don't even know why he would
want
to
achieve this, he hasn't told me. Is there way of doing this at all -
maybe
using VBA?