Automatically Adding 20% to an Entry

by Allen Wyatt
(last updated March 2, 2019)

1

Julie would like to have some input cells on a worksheet that, when someone enters a value, automatically add 20% to whatever was entered. For instance, if someone enters 200 into one of these cells, what is actually entered is 240.

There are a multitude of ways you can approach this task using macros. The best approach is to create a macro that is run, automatically, when a cell is changed in the worksheet. You can then check to see if the change was made in one of the input cells and adjust values accordingly. The following example modifies the entered value if it was made in any of three cells: A1, C3, or B8.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInput As Range
    Dim rInt As Range
    Dim rCell As Range

    'change the input cell range as desired
    Set rInput = Range("A1, C3, B8")

    Set rInt = Intersect(Target, rInput)
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            If IsNumeric(rCell) Then
                With Application
                    .EnableEvents = False
                    rCell = rCell * 1.2
                    .EnableEvents = True
                End With
            End If
        Next
    End If
End Sub

Remember that this is an event handler, which means it is triggered (in this case) whenever something changes in the worksheet. In order to use this macro, right-click on the worksheet tab and choose View Code from the resulting Context menu. Excel displays the VB Editor and you can then add the Worksheet_Change code.

Note that the key to figuring out whether the change was done in one of the three defined input cells is the Intersect function. It checks to see if there is an intersection between the Target range (the changed cells that triggered the Worksheet_Change handler) and the rInput range (your input cells). If there is, then rInt would contain the cells that did intersect.

The macro then steps through those cells and, if the cells contain numeric values, it multiplies those cells by 120%. (Multiplying by 120% is the same as increasing the value by 20%.) Note that the .EnableEvents property is set to False when the multiplication is done; if this safeguard were not taken, then each multiplication would trigger this event handler again and you would repeatedly (and forever) multiply the cell value by 120%.

If you wanted to actually do some other processing to the values—such as rounding to a specific number of decimal points or to an integer value—then you simply need to make a change to the single line that actually does the multiplication.

If your input cells are in a contiguous area, a better approach might be to define those input cells as a named range, and then use that named range within the macro to determine the intersection of the changed cells. In that way you don't need to modify the macro when or if your group of input cells change.

To use this approach, let's say that your range of input cells is B7:B19. Select those cells and then, using the name box at the upper-left corner of the cells area, enter the name "plus20pct". This action assigns the name to the range. You can then use that name within the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range

    'change the input cell range as desired

    Set rInt = Intersect(Target, Range("plus20pct"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            If IsNumeric(rCell) Then
                With Application
                    .EnableEvents = False
                    rCell = rCell * 1.2
                    .EnableEvents = True
                End With
            End If
        Next
    End If
End Sub

Note that the only change is in the way that the intersection of cells is determined—the Intersect function uses the "plus20pct" range as a parameter. Everything else works as before.

Now that you've seen how to do this using macros, the question remains as to whether you really should do it using macros. First, there are limits to what these macros can do. For instance, what if your user enters a date or time into one of the input cells? Internally, Excel handles dates and times as numbers, meaning that they, too, would be increased by 20%.

Second, you need to consider what happens to your worksheet if someone modifies the worksheet structure by adding or deleting rows or columns. The macros are using either absolute cell references (A1, C3, and B8) or a named range (plus20pct). While the named range may be adjusted by row or column addition or deletion, the absolute cells references would not change. Thus, you could end up with the macro checking (and adjusting) cells that are no longer the expected data entry cells.

Third, let's say that someone enters a value (200) into one of your input cells. It is automatically increased by 20% and becomes 240. The person sees this change and wondered what happened, so they select the cell and press F2 to start to edit the cell. Before they make the change, they remember that "Oh, yeah; it is supposed to increase by 20% automatically." So, they simply press Enter to accept the 240 value. However, Excel sees this as a change and increases the 240 by 20%, resulting in 288—not what you or the user intended.

This second consideration—user confusion—is the biggest potential problem with automatically changing what a user enters into the worksheet. A less confusing approach would be to have a well-defined input area for your workbook. The user puts figures into the input area and those figures remain as they input them. Then, in other cells or in your formulas, you do the adjustment by 20%.

This design approach (modifying the worksheet design for easy data entry) is potentially less confusing to the user than automatically changing what they entered in a worksheet cell. It also gets rid of a risk that is inherent with any macro-enabled workbook—the user could load the workbook without enabling macros, thereby ensuring that the figures are not adjusted as you intended.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12684) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Saving a Workbook Using Passwords

If you want to protect your workbook so that others cannot open or change the information it contains, an easy way to ...

Discover More

Searching for Character Formatting

Need to look for a piece of text possessing a particular formatting attribute? Here's the skinny on how this is accomplished.

Discover More

Setting an Upper Threshold for a Cell

Do you want to limit what can be entered into a particular cell in your worksheet? Here are three separate ways you can ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Inserting Different Dashes

Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.

Discover More

Not Enough Resources to Delete Rows and Columns

Few things are as frustrating as trying to delete rows or columns and having Excel tell you that you can't perform the ...

Discover More

Adding Spaces in Front of Capital Letters

Got some text that is "run together" and needs spaces inserted to improve readability? There are a variety of approaches ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 2?

2019-03-02 12:03:43

Willy Vanhaelen

It has no sense to use a loop in this macros since Target points to the cell(s) you are changing.

This much smaller macro does the job as well:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C3,B8")) Is Nothing Then Exit Sub 'adjust range as desired
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target * 1.2
Application.EnableEvents = True
End If
End Sub

This macro works with a named range as well even if it is a non-contiguous area. Simply replace Range("A1,C3,B8") with Range("plus20pct_or_whatever") which you can define as desired.

Unlike the assertion in this tip, by entering a date the macro will not change it because unlike Excel, VBA doesn't consider a date as numeric. You will be in trouble though because by entering a date, excel formats that cell as date and if you enter a number afterwards,VBA will consider it as a date and will do nothing. You can fix this by formatting the cell back to General or Number.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.