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, please 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.

Sub AddNumber()
Dim ws As Worksheet
Dim rngSel As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection
lRows = rngSel.Rows.Count
lCols = rngSel.Columns.Count

'optional -- change this number
Num = 7 

If rngSel.Count = 1 Then
   rngSel = rngSel + Num
Else
   Arr = rngSel
   For i = 1 To lRows
      For j = 1 To lCols
         Arr(i, j) = Arr(i, j) + Num
      Next j
   Next i
   rngSel.Value = Arr
End If

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.

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

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

If Num <> 0 Then
   If rngSel.Count = 1 Then
      rngSel = rngSel + Num
   Else
      Arr = rngSel
      For i = 1 To lRows
         For j = 1 To lCols
            Arr(i, j) = Arr(i, j) + Num
         Next j
      Next i
      rngSel.Value = Arr
   End If
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, please watch this video tutorial.

Or watch on YouTube: Add Number to Multiple Cells in Excel

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.

More Tutorials

Increase Numbers With Paste Special

Get Started With VBA

Copy VBA Code to Your Workbook

 

Search Contextures Sites

 

 

30 Excel Functions in 30 Days

 

 

 

 

Last updated: April 6, 2016 7:44 PM