Macro to Perform Arithmetic Operation to a Selection

This VBA macro does maths on every number in the selected cells at once: add GST, increase prices by 10%, or convert rupees to lakhs. Text, blanks and formulas are left alone.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

One macro for all four operations

Select the numbers, run the macro, then choose the operation and the number:

Sub MathOnSelection()
    Dim op As String, n As Double, c As Range
    op = InputBox("Operation: + - * or /", "Maths on selection", "*")
    If op = "" Then Exit Sub
    n = Application.InputBox("Number:", Type:=1)
    If op = "/" And n = 0 Then Exit Sub
    For Each c In Selection
        If Not IsEmpty(c) And Not c.HasFormula And IsNumeric(c.Value) Then
            Select Case op
                Case "+": c.Value = c.Value + n
                Case "-": c.Value = c.Value - n
                Case "*": c.Value = c.Value * n
                Case "/": c.Value = c.Value / n
            End Select
        End If
    Next c
End Sub

Examples

To Choose Number
Add 18% GST * 1.18
Increase prices by 10% * 1.1
Remove 18% GST / 1.18
Convert rupees to lakhs / 100000
Add 5 days to dates + 5

Without a macro: Paste Special

  1. Type the number, such as 1.18, in an empty cell and copy it.
  2. Select the cells to change.
  3. Press Ctrl + Alt + V, choose Multiply (or Add, Subtract, Divide) and click OK.
  4. Delete the helper cell.

Things to know

  • Results replace the original numbers. Save a copy first, because a macro can’t be undone.
  • Formulas are skipped, so their results don’t get changed twice.
  • Dates are numbers in Excel, so + 5 moves them five days later. See the GST calculation page for formula versions.

Related macros