Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Refernce error | Excel Discussion (Misc queries) | |||
Circular Reference Error Even Though There Is No C.R. | Excel Worksheet Functions | |||
Circular Reference error... | Excel Discussion (Misc queries) | |||
Circular reference error | Excel Discussion (Misc queries) | |||
Turning off Circular Error Help | Excel Discussion (Misc queries) |