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

Getting Word to Remember the Default Date and Time Format

One way to insert the current date into your document is to use the Date and Time dialog box. The Default button in the ...

Discover More

Quickly Copying Worksheets

Excel provides a little-known way to copy worksheets simply by clicking and dragging. Here's how to do it.

Discover More

Indexing Based on a Range of Letters

Word provides many options for creating indexes. One option allows you to specify that the index contain only entries ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Scroll Wheel Doesn't Work when Editing

Using the mouse's scroll wheel can help improve how you edit information in a worksheet. Here's how to make sure that the ...

Discover More

Copying Data without Leaving the Currently Selected Cell

Copying from one cell to another is easy when editing your worksheet. Doing the copying without selecting a cell other ...

Discover More

Deleting All Names but a Few

Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy ...

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 9 - 5?

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.