circular error

  • Thread starter Thread starter gavin
  • Start date Start date
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?
 
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
 
Hi Gord,
Many thanks for your fast reply - it really is appreciated. I'll give this a
go tomorrow.


Best wishes,


Gavin
 
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
 
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
 
Back
Top