# Add Number to Range of Cells

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

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

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

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

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

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.

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.

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

Last updated: December 9, 2016 4:45 PM