Contextures

Add Number to Range of Cells

Add a specific amount to the numbers in a range of cells, either manually, or with a macro.

Add With Paste Special

To increase the amounts in a range of cells -- adding the same amount to each cell -- you can use the Paste Special command.

For example, follow these steps to increase the amounts by 10, in cells A2:B6

  1. In an empty cell, type 10, then copy that cell
  2. Select cells A2:B6
  3. On the Ribbon, click the Home tab
  4. Click the Arrow below the Paste button, and click Paste Special -- or use the shortcut keys -- Ctrl+Alt+V

    paste special command

  5. In the Paste Special window, under Operation, click Add, then click OK
  6. All the numbers increase by 10
  7. Clear the cell where you typed the 10

paste special command

Video: Add Numbers with Paste Special

To see the the steps for adding numbers with the Paste Special command, watch this short video.

Macro: Add Specific Number to Cells

This macro adds a specific number to all the selected cells. In this example, the Num variable is set to 7, to make it easy to add one week to cells that contain dates.

NOTE: Make a backup of your file, before using this macro.

Sub AddNumber()
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection

Num = 7

For Each rng In rngSel.Areas
  If rng.Count = 1 Then
     rng = rng + Num
  Else
      lRows = rng.Rows.Count
      lCols = rng.Columns.Count
      Arr = rng
      For i = 1 To lRows
         For j = 1 To lCols
            Arr(i, j) = Arr(i, j) + Num
         Next j
      Next i
      rng.Value = Arr
  End If
Next rng

End Sub

Add Number to Cells With VBA

This macro prompts you for a number, then adds that number to all the selected cells.

In the input box, the default number is set as 7, and you can overwrite that with any number (whole or decimal, positive or negative.).

paste special command

NOTE: In the input box, the default number is set as 7, so it's easy to add a week to a group of dates. You can change the default, if you usually add a different number.

IMPORTANT: Make a backup of your file, before using this macro.

Sub AddNumberPrompt()
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Dim strPrompt As String
Set rngSel = Selection
lAreas = rng.Areas.Count
strPrompt = "Enter number to add to selected cells"

On Error Resume Next
Num = InputBox(strPrompt, "Number to Add", 7)

If Num <> 0 Then
  For Each rng In rngSel.Areas
     If rng.Count = 1 Then
        rng = rng + Num
     Else
        lRows = rng.Rows.Count
        lCols = rng.Columns.Count
        Arr = rng
        For i = 1 To lRows
           For j = 1 To lCols
              Arr(i, j) = Arr(i, j) + Num
           Next j
        Next i
        rng.Value = Arr
     End If
  Next rng
End If

End Sub

Video: Add Number With Macro or Paste Special

To see the steps for using Paste Special, and the steps for modifying the Add Numbers macros, watch this video tutorial.

Download the Sample File

To download the sample file, click here: addnumbertocells.zip

The zipped file is in xlsm format, and contains macros. Enable macros, if you want to test the code in the sample file.

NOTE: Make a backup of your file, before using these macros.

More Tutorials

Increase Numbers With Paste Special

Get Started With VBA

Copy VBA Code to Your Workbook

 

 

Get weekly Excel tips from Debra

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

Last updated: August 4, 2018 10:20 AM