Excel: Add % value to same cell as you type in

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

etonlau

Thread Starter
Joined
Mar 22, 2015
Messages
4
I want to ad 20% to the same cell I'm typing the value in, how do I do that ?
Example: I type 100 in cell L2 and when I move away from L2 it should add 20% and show 120.
 
Joined
Oct 5, 2008
Messages
386
Only way I can see is to add a helper column with formula in the column where figure would normally go.
Suggestion attached

Pedro
 

Attachments

Joined
Jul 25, 2004
Messages
5,456
Hi there, welcome to the board!

The easiest and safes way to do what you're asking is to do it exactly like Pedro15 suggested and use a helper column. Not only does this give you the desired answer, but it preserves the original value of the input data. It is HIGHLY RECOMMENDED that you separate input from output, and the suggestion is one very good way of doing that. Besides, it's not always good practice to just somewhat arbitrarily change values someone enters.

That being said, we can do this automatically with code by using a worksheet change event. I'd stay away from this unless you absolutely have to. Trust me, while this seems like an easier solution on the outside (it's just plug-n-play without any extra messy columns) it's the least favored. Now I've beat my drum it's up to you to decide.

So, the code below is what you want. There are two constants you need to change and I've commented pretty big where they are. They are the lines which start with "Const" which means "constant". The first one is the cell address you want to always add 20% to (well, 120% really) and the other is the amount to add.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Cell As Range
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' CHANGE THESE TWO CONSTANT VALUES TO WHAT YOU WANT
    'Set cell address to change here
    Const AddPercentCell As String = "A1"
    Const PercentToAdd As Single = 20
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    'Loop through each changed cell
    For Each Cell In Target.Cells
        If Cell.Address(0, 0) = AddPercentCell Then
            If Len(Cell.Value) > 0 And IsNumeric(Cell.Value) And Not IsDate(Cell.Value) Then
                Application.EnableEvents = False
                Cell.Value = Cell.Value * ((100 + PercentToAdd) / 100)
                Application.EnableEvents = True
            End If
        End If
    Next Cell
    
End Sub
To install this code, just right-click the worksheet tab of the desired worksheet and select View Code, then paste it into the big white window pane that shows. You can then close that window (that's the Visual Basic Editor, or VBE) or just press ALT+Q to return to Excel (although the VBE will remain open if you do that, which won't harm anything, but it's another open window).

Note you'll have to have the file saved in anything except XLSX format, because you can't save macros in that file format. You want XLSB, but you can use XLSM. I can go into several reasons why it's better to use XLSB, but I've been long-winded enough, and I'm just going to say I know better and take my word for it. ;)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top