1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by etonlau, Apr 9, 2015.

Thread Status:
Not open for further replies.
  1. etonlau

    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.
     
  2. Pedro15

    Pedro15

    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
     

    Attached Files:

  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. ;)
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1146297

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice