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

## Get the Sample File

To get 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

AutoFill Examples

Get Started With VBA

Copy VBA Code to Your Workbook

Last updated: July 11, 2021 7:30 PM