Automatically Adding 20% to an Entry

by Allen Wyatt
(last updated October 7, 2016)

5

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 delting 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.

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

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

Deleting Datasheet Rows or Columns

How to remove information from your datasheet.

Discover More

Using Custom Number Formats

Most formatting needs are met by using the predefined formatting options in Excel. The program also allows you to move beyond ...

Discover More

Resizing Table Columns with the Mouse

Once a table is inserted in your document, you can use the mouse to adjust the width of columns. The effect the mouse pointer ...

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)

Recording a Data Entry Time

When entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are ...

Discover More

Copying to Very Large Ranges

Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of cells ...

Discover More

Quickly Entering Dates and Times

Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow entering ...

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 8Mpixels. 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 7 - 0?

2016-10-07 10:04:35

Bob C

Why not simply enter "+100*1.2", "+200*1.2", etc.


2013-10-22 12:03:02

Willy Vanhaelen

JC, you are quite right about the error handler. So I adapted the macro. While doing some tests, I found that in fact there is no need for a For Next loop. When you select multiple cells and press [Ctrl]+[Enter], Excel fills you selection with your entry and then runs the Worksheet_Change macro. This one line "rInt = rInt.Cells(1) * 1.2" replaces the For Each loop. It multiplies the number in the range's first cell by 1.2 and fills the entire range with the result. So, job finished! Of coarse this works as well when your selection is only a single cell.

This macro also prevents that a zero appears instead of an empty cell when clearing cell contents.

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't bother about non-numeric entries or empty cells
If Not IsNumeric(Target.Cells(1)) Or Target.Cells(1) = 0 Then Exit Sub
Dim rInt As Range
Set rInt = Intersect(Target, Range("plus20pct"))
If rInt Is Nothing Then Exit Sub 'Quit if the ranges don't intersect
On Error GoTo Enable_Events
Application.EnableEvents = False
rInt = rInt.Cells(1) * 1.2
Enable_Events:
Application.EnableEvents = True
End Sub


2013-10-19 18:24:46

Julie

I would like to thank everyone for their help, it was much appreciated.


2013-10-19 11:58:42

JC

If you choose to go the macro route you can handle the third caveat by using the worksheet SelectionChange event to store the value of the selection. Then you can determine if the value truly changed within the worksheet Change event. But this can get tricky.

Willy, I believe the author's intent in the example was to alert those not familiar with the EnableEvents property. In my opinion, whenever setting EnableEvents to false within a procedure you should ALWAYS have an error handler for that proc that sets this property back to true.


2013-10-19 09:41:59

Willy Vanhaelen

1) For the second approach your cells do not necesserly need to be adjacent. You can perfectly assign a single range name to a series of non-adjacent ranges. 'Intersect' works perfectly with them.

2) Why set the EnableEvents to False and True for each rCell over and over? Better do it globally for the entire For Each loop.

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

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


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.