circular error

G

gavin

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?
 
G

Gord Dibben

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
 
G

gavin

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


Best wishes,


Gavin
 
G

Gord Dibben

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
 
G

gavin

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


Regards


Gavin
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top