Solved

# format calculator in same cell

Posted on 2014-04-29
153 Views
Last Modified: 2014-05-15
Is there anyway in excel, if a user enters a value in a cell, i.e. 4, it automatically performs a calculation to change that value. For example in A1, if a user types 4, in cell B1 I have the value 4 pre-populated, so I want A1 to show the output of the formula

A1*B1,

so if the user types 4, it will return 16. I cant see anyway to do this via formatting.
0
Question by:pma111
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 3
7 Comments

LVL 51

Expert Comment

ID: 40029170
Hi,

Why don't you want the result in another cell?

Regards
0

LVL 15

Expert Comment

ID: 40029178
Hello,

I don't think so that the same can be done.

So Rethink on it....
0

LVL 27

Expert Comment

ID: 40029195
Put this code in the worksheet module:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Target.Value * Target.Offset(, 1).Value
End Sub
``````
0

LVL 51

Expert Comment

ID: 40029207
Hi,

Of course it is possible to make it but it is bad design to have input and result at the same place
Say your are tipping 12 answer a call and do not remember what you tipped you won't know whether 12 is the input or the result.

Regards
0

LVL 27

Accepted Solution

MacroShadow earned 250 total points
ID: 40029216
Sorry, this is the code you need:
``````Dim blnFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Not blnFlag Then
blnFlag = True
Target.Value = CLng(Target.Value) * CLng(Target.Offset(, 1).Value)
End If
End If
blnFlag = False
End Sub
``````
0

LVL 51

Assisted Solution

Rgonzo1971 earned 250 total points
ID: 40029245
@MacroShadow

Wouldn't be easier to disable the events instaed of a flag

``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
Target.Value = Target.Value * Target.Offset(, 1).Value
End If
Application.EnableEvents = True
End Sub
``````
0

LVL 27

Expert Comment

ID: 40029256
Yes it would. Although personally I wouldn't recommend it without thoroughly examining the workflow.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month3 days, 9 hours left to enroll

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.